Information in linked cells not updating properly

[mesteptoe]

I have a large number of files which include cells linked to a Summary sheet. I need to add some rows to the Summary sheet but in doing so I notice that the cells of the linked files do not pick up that the original link location has shifted. I have experimented using both the eg. A%5% format and A5 in the linked files but either way they refer to the original location on the summary file. How do I get the linked files to recognise the information in the summary file has moved?

Thank you.

Graham

 

[GRTorres]

You can experiences two situations with linked cells from different files (or workbooks)

To link cells between two workbooks, you just switch between the workbooks and hit equal then make selection. Easy enough. It will give you the format of: = [OtherWorkbook.xls]Sheet1!$A$5

Because excel will place absolute markers "$" infront of your cell reference that link will always go to cell A5, but

Situation 1) If both workbooks are open, you can move that selection cell anywhere you want and your link will follow it, you dont have to even worry about the absolute signs, excels makes that connection for you. But only if both are open. Same applies if you are updating values, iit will do so automatically (if turned on in excel options).

Situation 2) If one is closed and you have previously moved your link, then excel cannot follow your path and will return a zero of #VALUE or something. It will keep the absolute cell reference and will be unable to update. Also, say you havent moved your refernce and the link is good, but one workbook is still closed, then excel wont be able to update the value if it has been changed, but rather remember the last known value.

My suggestion, either keep the master file open at all time when making changes to other books, or in some way combine them. Hope this helps.