Changing link in Destination Workbook (2007)

Topic

New Reply

I have a workbook named Profit Link. the values in this workbook link to values in a workbook named Cafe Brazilia Sheet1.

Whenever I insert new rows in Cafe Brazilia , the values in Profit link are not linked to the correct row number in Cafe Brazilia.

How can i prevent this from happening-i.e if I insert new rows in Cafe Brazilia, then the cells in Profit Link must refer to the correct cell address in Cafe Brazilia?

Your assistance in this regard will be most appreciated

Howard

Reply | Quote

Replies

Please provide detailed information. Just “the values in Profit link are not linked to the correct row number” doesn’t say much.

Reply | Quote

Hi Hans

Thanks for the reply. I have attached a sample of my destination workbook, which links to Cafe Brazilia. If I insert new rows in Cafe Brazilia, then the linked row doe not fwere to the correct row in Cafe Brazilia.

It would be appreciated if you would assist

Regards

Howard

Reply | Quote

If possible, follow mbarron’s suggestion of named cells. The disadvantage is that you’ll have to name the inserted cells too if you want to refer to them.

Reply | Quote

Hi Hans

Thanks for the reply. Mbarron’s latest suggestion is even better than his first.

Regards

Howard

Reply | Quote

It would be even easier if you placed the sheets in the same workbook, but of course that is not always possible.

Reply | Quote

Hi Hans

It would be if there was only one workbook to link to. The example I posted contained only a link to one workbook. I have 13 workbooks to link to. What I will do in future is to have the workbook “profit Link” open when I insert rows in any of the workbooks that “Profit Link” refers to

Once again thanks for all the input. This site have been a valuable source of information for myself. I have gained tremendous knowledge from all the contributors. The responses have been rather quick.

Regards

Howard

Reply | Quote

It sounds to me like your formulas are using specific locations. i.e. A$6$ This means that if you insert a row before row 6 everything will move down one and your calculation will be pointing at the wrong cell. Given the address I provided it would still be pointing at A6, but the value would now be the one that used to be in A5. Now, without testing I cannot give correct information on how to handle this, but I thought that this might get us started in the right direction. Please let me know if I am even close and then I will start trying to find an answer to the issue.

Reply | Quote

Hi Timelord

Thanks for the reply , you are on the right track

Regards

Howard

Reply | Quote

Instead of linking on a cell, link on a Named Range.

Click on the cell you want to link, go to InsertNameDefine… and give it a name.

In your linked sheet, change the formula to reference the Name of the cell instead of the cell reference.

Reply | Quote

Thanks for the reply. I thought that I would have to do this, but thought that there may be an easier way

Regards

Howard

Reply | Quote

If you have both books open while you are inserting the rows, the references in the linked book will “self adjust”.

Reply | Quote

Thanks for the reply.

Your suggestion is great. I will open the workbook “Profit Link” each time I insert rows in the workbooks that “Profit Link” links to..

Regards

Howard

Reply | Quote

mbaron is correct, if both are open while making the change it is taken care of right away. The naming will also work, but will take a bit of effort to setup.

Reply | Quote

Hi Timelord

Thanks for the reply.

Will use Mbarron’s latest suggestion by keeping “profit Link” open when inserting rows in the source workbooks.

Regards

Howard

Reply | Quote