Relative external references (links)

**aero_b** · 01-10-2011, 01:02 PM

Hello,

From what I've read so far, it's not possible to make an external reference relative. What I would like is for a cell in [WorkBook1]Sheet1 to have the same value as a cell in [WorkBook2]Sheet1, and for any modification in [WorkBook1]Sheet1 to be reflected automatically in [WorkBook2]Sheet1. This is easy enough to do as long as you never (EVER!) move the files, using external references (a.k.a. links). I need to move the files around, although I would always keep the folder structure around my cross-referenced files the same. Relative paths would be great, but MS doesn't seem to think that's important.

Here's what I tried to do to outsmart Excel. I made cell A1 of the the sheet with all the x-refs contain the path of the file it is in (result: 'M:\Research\Thesis\DCB Tests\DCB Data\DataReduction\). I then made a few cells that contained the names of the x-referenced files in the appropriate format (e.g. in cell A2: [C02-04_AC_DataReduction_IIWN.xlsx]Report'!). Then, in the spot that I wanted the x-ref, I used the INDIRECT function: =INDIRECT(A1&A2&"F34") to refer to in 'M:\Research\Thesis\DCB Tests\DCB Data\DataReduction\C02-04_AC_DataReduction_IIWN.xlsx]Report'!F34.

For some reason, this only works when the file I'm referring to is open, even though it now has a full-path reference which shouldn't need it to be open. Any ideas (I know I could write a macro that would do this for me, but didn't think I would need one)?

**6StringJazzer** · 01-10-2011, 02:07 PM

The INDIRECT function requires the target file to be open if it has an external reference.

I have used external links that reference another workbook in the same directory, and if the both workbooks are moved or copied to another directory it still works. In fact I just tried it to make sure, works like a charm. Not sure why that doesn't seem to be working for you.

When you say "relative" link be aware that when you have links internal to the same workbook, they will follow the data if you add or delete rows or columns. That is, if you link to Sheet1!D5 and then you delete row 4, that reference will automatically be updated to Sheet1!D4. This automatic adjustment does not happen if you link to another workbook. But based on your description, that is not your issue.

**aero_b** · 01-10-2011, 02:33 PM

Hi 6String,

I see you understood my question and answered it very helpfully. I will try what you are suggesting, but I'd like to ask how you create the link to the workbook in the same folder (this requirement that the two files be in the same folder is not a problem).

The way I do it is to enter "=" then click on the desired cell in the other workbook. As long as that other workbook stays open, the cell formula will say something like "=[C03-16_AC_DataReduction_IIWN.xlsx]Report'!F36", but as soon as I close the other workbook, it changes to something like "='M:\Research\Thesis\DCB Tests\DCB Data\DataReduction\[C03-16_AC_DataReduction_IIWN.xlsx]Report'!F36".

Is it so that if I save the file with the above explicit (long) path, then move it to my flash drive, it'll then change the cross-reference to "E:\ ... " if E:\ is my flash drive? Or is there another way to create the cross-reference which stops Excel from trying to convert the "shorter" path to the full path?

**aero_b** · 01-10-2011, 04:05 PM

I just tried it and saw that it changes the full path according to which location I open the file from. I could see this causing problems for other people, but it turns out to be the behaviour that I want, so I won't complain.

Thanks for your help!

**6StringJazzer** · 01-10-2011, 05:46 PM

Originally Posted by aero_b

I just tried it and saw that it changes the full path according to which location I open the file from.

Yes, I was going to kindly suggest that you try it for yourself. Teach a man to fish, and all that.

This is one feature of Excel that I find odd. I don't know why it displays the full path name when what it is really storing is a relative path. It is very confusing. And I can't quite remember what you need to do if you really do need the full absolute path addressing.