1
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
Is what you are asking just copying the hyperlinks to the workbook? – CharlieRB – 2012-01-26T13:42:55.357
@CharlieRB I'm not sure if I understand your question, but I mean extracting the destination of the hyperlink to use in a formula...I've updated the question – clabacchio – 2012-01-26T13:47:33.587
If I understand it now, you want to be able to use a formula to extract the hyperlink path (address) from the reference in the first workbook. If that is the case; are you using the "=HYPERLINK" function to link to the other workbooks. Or are you using the "Insert Hyperlink" command from the menu? – CharlieRB – 2012-01-26T13:59:21.680
@CharlieRB yes, I've used the context menu, but is it different from using the formula? – clabacchio – 2012-01-26T15:03:17.340