Maintain formula "Links" when sheet moved

[pdmkh]

Is there a way to maintain established links when a work book is moved. I will have a cell formula that returns information from another workbook. Any time the workbook is moved or remnamed I have to manually update all the formulas. Is ther any way to lock this formula link. Example formula provided.

Please Login or Register  to view this content.

[bentleybob]

Have all workbooks that link to the file open when you move or rename the linked file(s), and always open the linked files and use SaveAs (rather than moving and/or renaming a closed file).

The only other option is to use INDIRECT, which would allow you to put the file name and path in a single cell somewhere in the linking file and then changing that cell when the file is moved or renamed. The disadvantages to this approach are that all your linking formulas need to be changed (once, and global search and replace makes this easier) and INDIRECT is a volatile function, which has its own set of problems. Solution #1 is much preferred.