r_johnston
asked on
Automatic Workbook Link Updating
I have two workbooks (Calling.xlsx, Source.xlsx); Calling.xlsx links to Source.xlsx
I would like to have the folder address update automatically in Calling.xlsx if the workbooks are moved to another folder; effectively auto updating links to the other worksheet.
I have found the following formula on Google somewhere: =LEFT(CELL("filename",A1),
So this formula would return, for example, the following: C:\Users\User\Desktop
My problem is trying to concatenate it with a workbook, sheet and cell reference so that the formula would work like a direct link made manually:
='C:\Users\User\Desktop\[S
I would like to have the folder address update automatically in Calling.xlsx if the workbooks are moved to another folder; effectively auto updating links to the other worksheet.
I have found the following formula on Google somewhere: =LEFT(CELL("filename",A1),
So this formula would return, for example, the following: C:\Users\User\Desktop
My problem is trying to concatenate it with a workbook, sheet and cell reference so that the formula would work like a direct link made manually:
='C:\Users\User\Desktop\[S
Unfortunately, INDIRECT does not work when your source file is not open.
Likewise, I am not sure that would fix your issue.
If your source file gets moved, when the calling file is opened it would give an error that it cannot find the Source file; the Edit links window can then be called to adjust the file location.
If both files get moved at the same time, there won't be an issue; the link between them although to us it looks like a folder structure, the system will realise that the files are in the same folder and adjust accordingly; if not in the same folder then the number of levels up the hierarchy and back down another hierarchy will be mapped rather than a physical location.
If both files are open and the Source file gets saved to another location the link will change automatically.
Likewise, I am not sure that would fix your issue.
If your source file gets moved, when the calling file is opened it would give an error that it cannot find the Source file; the Edit links window can then be called to adjust the file location.
If both files get moved at the same time, there won't be an issue; the link between them although to us it looks like a folder structure, the system will realise that the files are in the same folder and adjust accordingly; if not in the same folder then the number of levels up the hierarchy and back down another hierarchy will be mapped rather than a physical location.
If both files are open and the Source file gets saved to another location the link will change automatically.
Look if my process for updating links is helpful to you
Updating Excel Links in Thousands of Files
https://www.experts-exchange.com/articles/31867/Updating-Excel-Links-in-Thousands-of-Files.html
Updating Excel Links in Thousands of Files
https://www.experts-exchange.com/articles/31867/Updating-Excel-Links-in-Thousands-of-Files.html
I am not sure if I understood your question, but if you want to get the full path with the current sheet name and cell reference, you can use this formula
=CELL("filename",A1)&ADDRE
=CELL("filename",A1)&ADDRE
ASKER
It would be preferred to not have to run a program to update a few links...primarily because the target audience is not that tech savvy...
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
You can use INDIRECT with a text string making the cell address.
Syntax =INDIRECT(A1)
INDIRECT works by looking at the reference (as above A1) and rather returning the contents of A1 (like =A1 would) it looks at the contents of A1 and evaluates that as a reference and returns the contents of that reference instead. So, if A1 contained 'Sheet1'!$A$4 the INDIRECT(A1) would return the contents of Sheet1!A4.
The string within the INDIRECT can be formulated rather than hard-coded.