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

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

clabacchio

Posted 2012-01-26T12:21:56.160

Reputation: 424

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

Answers

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")

Rachel Hettinger

Posted 2012-01-26T12:21:56.160

Reputation: 481

I've edited the question, take a look :) – clabacchio – 2012-01-26T16:12:09.313

Rachel is correct. If you want to link to a value, it's different than using the HYPERLINK function. To do it, open both files, select the cell you want the value to show in, type the equal sign and then physically go select the cell that has the data you want from the other workbook. This will create a reference to that value similar to what she has shown you here. – CharlieRB – 2012-01-26T17:01:06.347

Just to clearify; you would use the HYPERLINK function to open the other file from the cell you type the function into. – CharlieRB – 2012-01-26T17:02:33.443

Check the link I've inserted into the question – clabacchio – 2012-01-26T19:21:29.353

@clabacchio I updated my answer to include another option. However, you either need to directly link to the external file (first example), or the external file needs to be open (second example). – Rachel Hettinger – 2012-01-26T21:26:48.623

Now I understand better how HYPERLINK and INDIRECT work; but the former wants the relative path, while the latter wants the workbook open and only the filename: there is a way to make them work together on the same file? – clabacchio – 2012-01-27T10:29:13.473

@clabacchio No, sorry. Those are your choices. – Rachel Hettinger – 2012-01-28T16:46:00.970

What do you mean? – clabacchio – 2012-01-28T17:10:23.937

In order to link to a cell in another workbook you can enter the link as shown in my first example or use the INDIRECT function. – Rachel Hettinger – 2012-01-29T04:32:58.093

If you don't want to have the path in a cell. You might insert a name in the worksheet with the path parameter referring to path="F:Reports\" for instance

Then in the cell =HYPERLINK(path&"file.xls","link1")

Just a little cleaner because someone won't accidentally clear the cell with the path here is an example of a path name inserted as a name

enter image description here

then use this as your formula =HYPERLINK(path&"Sheet1!$B$3", "link1")

datatoo

Posted 2012-01-26T12:21:56.160

Reputation: 3 162

Ok, but it remains the problem of taking that path and using it for taking the value a specific cell... – clabacchio – 2012-01-26T16:37:48.553

Perhaps I am misunderstanding. In the name I suggest you insert the actual path not a cell address My example appends the file, but you can make the path include the filename as well and then &theCellAddress – datatoo – 2012-01-26T17:17:50.633

Maybe I didn't understood but my problem is: I can do what you say, up to specify the filename, and even the cell; but when I try to pick the value, es ='[filepath]Sheet!cell', it opens a window in which asks me to choose the destination file. Where am I doing wrong? – clabacchio – 2012-01-26T19:16:51.560

I edited my answer adding an example – datatoo – 2012-01-26T20:30:34.930

But this is an hyperlink to that cell right? – clabacchio – 2012-01-26T20:44:07.540

the hyperlink in my example ends up pointing to "[F:\Reports\Sales_2010.xls]Sheet1!$B$3" so $B$3 on Sheet1 of that file – datatoo – 2012-01-27T00:04:56.623

Ok but what I want from the cell is to copy the value...this is just a clickable reference, or I'm wrong? – clabacchio – 2012-01-27T08:03:08.197