Prevent links from updating when saving

[xgary.wilsonx]

Good afternoon

I have 2 work books called book 1 and book 2, cell A1 (in sheet 1) in book 2 is linked to cell A1 in book 1 by the simple formula ='[book1.xlsx]sheet1'!$A$1. However i regularly have to save and rename book 1 as ABC123, if i do this whilst book 2 is open then the link in book 2 changes to ='[ABC123.xlsx]sheet1'!$A$1.

Is there anyway to stop this from happening and keep the link to book 1, apart from making sure book 2 is closed, could a popup be created to advise that book 2 is open?

Any ideas greatly appreciated

Many thanks


Gary

 

[xgary.wilsonx]

Hi
Have you considered using the indirect formula, if you were to add the name of the workbook (A1), sheet (A2), and cell (A3) that you want to link to in book 1 in book 2, then in the cell (A4) enter the formula =INDIRECT("'["&A1&".xlsx]"&A2&"'!"&A3) and this will return the value you wanted. however this only works with both workbooks open.

 

[xgary.wilsonx]

Thats great Gary, Thanks very much - you rock

 

[xgary.wilsonx]

You're very welcome