I don’t believe I don’t have this here yet. This is one of my oldest tricks
When we have to do links between two workbooks (One cell in Workbook1 to have value updated from a cell in Workbook2), we usually just do links like this…
Go to workbook1, select a cell, type in ‘=’ then go to Workbook2 and select a cell, then ‘ENTER’
To get something like this
Which will look like this if try1.xlsx is closed
However, if you open try2.xlsx alone before you open try1.xlsx, you get this message
This means now try2.xlsx and try1.xlsx have a link between them
In another word, try2.xlsx liknks to try1.xlsx
Which is ok, but
Except, I don’t like that,
It will take me some time to refresh the link if I have moved that try1.xlsx to another location or rename it.
So, what the solution?
Dynamic link
Which basically, create a link between those two without the mess, and Excel will not recognize it as a link and will stop complaining.
How?
Using the function =INDIRECT(), one of my favorites.
So, in above example, we break in the link inside C12 into pieces, like below
So, C4 has the full folder location of our workbook try1.xlsx
C5 has the file name
C6 has the sheet name
And C7 has the cell
Now, we combine them back into full path as we found it originally into cell C8
By adding the required characters to make it look exactly how INDIRECT expects it
Then, finally, we use it inside INDIRECT
And here is the dynamic link
What is the gain?
Mostly, the flexibility of chagninng the references, if folder got changed (by moving the workbook) or name got change, by renaming it, or sheet, we can just type it in its box and press enter
That particular workbook, has to be open to read the value.
What are other gains?
Well, think about dynamically reading numbers from multiple sheets into one, for example, you have sheet per project and you want to collect all these info from all these projects into a ‘summary’ sheet, this dynamic link, can help you build one cell, then copy it to others to apply it there
