Relative external references (links)

[aero_b]

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