Link to another workbook

[dkubiak]

I have two workbooks: Experiment1.xls and Experiment2.xls and I want cell A1 in Experiement1 to display the information contained in cell B2 in Experiment2. How do I set up this external reference?

 

[GlennUK]

Open both of the workbooks. In cell A1 of Experiment1 type = and navigate to the other workbook and select the cell to reference and press Enter.

 

[dkubiak]

That does not seem to work. As soon as I navigate to the other workbook and click on the cell, it flashes back to the original workbook with no link.

I think I remember something about "external reference" to do this....

 

[GlennUK]

Both workbooks must be open in the same instance of Excel ... is that the case?

 

[aylafan]

Put this in Cell A1 on Experiment1.xls

Remember to name Sheet1 to whatever the sheet is named in Experiment2.xls

=[Experiment2.xls]Sheet1!$B$2

This is basically doing it the same way GlennUK mentioned above.

 

[dkubiak]

I typed the formula manually the way aylafan suggested, and this works so long as both files remain open. If I close and reopen them, the link is broken.

How do I set up a permenant link?

 

[aylafan]

You can always try a full path from your computer for closed workbooks.

example:

=('C:\Documents and Settings\nguyen\Desktop\Purchase Orders\[Experiment2.xls]Sheet1'!$B$2)

 

[dkubiak]

I am getting really frustrated here, as I know I done this before and all the instructions you have listed above should work. I even went here:

http://office.microsoft.com/en-us/e...ok-or-program-HP005199514.aspx?app=ZXL&ver=12

and followed those instructions. But, when I click over to the source workbook I abandon my destination workbook and the formula.

What is meant by opening a file in the "same instance" of excel? Is there a setting that controls this? Maybe that is my problem. I do have two icons down in my menu bar - one for each workbook.

 

[dkubiak]

Also, I the files are on a sharepoint, so I tried this:

=('http://sharepoint.12345.org/scm/projectmanagement/Templates/Project%20Tools/Experiment2.xlsx/Sheet1'B2)

 

[dkubiak]

I got it! Next time I just need to slow down and pay attention to all my characters/file endings/et cetera. For future reference:

=('http://sharepoint.12345.org/scm/projectmanagement/Templates/Project%20Tools/[Experiment2.xlsx]Sheet1'!$B$2)