I have an excel workbook (Global.xlsx) which contains hyperlinks (=HYPERLINK(…) ) to other workbooks (representing experiments I've taken, named Exp1, Exp2, etc.), and some data that describe the experiments.
Now I would like to copy in Global, values from cells in the ExpX workbooks, but using the link mentioned before as path for these references (so if I change the destination in the hyperlink, all the values are updated from the pointed file).
So, it's possible to extract the destination from an hyperlink and use it in a formula, in a way that changing the link also the values change?
Scheme of my idea:
- Many experiment workbooks, all with the same template
- A summarizing workbook, with links to these files, and some values picked from the linked workbooks
UPDATE: One solution could be writing the address of cell A1 of the ExpX file, then linking to that cell and picking values using OFFSET from that cell: but INDIRECT takes only the filename, while HYPERLINK wants the path.
This is a link to a google document that should give the idea
The target remains to have one only parameter to change for each file
Best Answer
You can directly link to a cell in another workbook like this:
If the external file name is in another cell, and the file is open, the
INDIRECT
function can be used like this where cellA1
contains the file name.