Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
Is what you are asking just copying the hyperlinks to the workbook? –  CharlieRB Jan 26 '12 at 13:42
    
@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 Jan 26 '12 at 13:47
    
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 Jan 26 '12 at 13:59
    
@CharlieRB yes, I've used the context menu, but is it different from using the formula? –  clabacchio Jan 26 '12 at 15:03
add comment

2 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")
share|improve this answer
    
I've edited the question, take a look :) –  clabacchio Jan 26 '12 at 16:12
    
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 Jan 26 '12 at 17:01
    
Just to clearify; you would use the HYPERLINK function to open the other file from the cell you type the function into. –  CharlieRB Jan 26 '12 at 17:02
    
Check the link I've inserted into the question –  clabacchio Jan 26 '12 at 19:21
    
@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 Jan 26 '12 at 21:26
show 4 more comments

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

share|improve this answer
    
Ok, but it remains the problem of taking that path and using it for taking the value a specific cell... –  clabacchio Jan 26 '12 at 16:37
    
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 Jan 26 '12 at 17:17
    
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 Jan 26 '12 at 19:16
    
I edited my answer adding an example –  datatoo Jan 26 '12 at 20:30
    
But this is an hyperlink to that cell right? –  clabacchio Jan 26 '12 at 20:44
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.