Links between two workbooks – dynamically using functions

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