Excel: make HYPERLINK and INDIRECT to work on the same sheet

hyperlinkmicrosoft excel

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:

='C:\Users\clabacchio\Documents\[Experiments.xlsx]Test1'!$A$2

If the external file name is in another cell, and the file is open, the INDIRECT function can be used like this where cell A1 contains the file name.

=INDIRECT("[" & A1 & "]Test1!$A$2")
Related Question