On Jun 23, 8:34 pm, Serena595
wrote:
Hi there, I'm hoping someone can help because this is driving me absolutely
crazy.

Filename1: c:\alloc\apple.xlsx
Filename2: c:\alloc2\apple.xlsx
Filename3: c:\compareapples.xlsx

Cell A1 in Filename1 = 100
Cell A1 in Filename2 = 200
Cell A1 in Filename3 contains formula:

'C:\Alloc\[apple.xlsx]Sheet1'!$A1-'C:\Alloc2\[apple.xls]Sheet1'!$A1

After all are closed and saved do the following:

1. Open Filename3 and verify that formula in cell 1 still looks like it is
suppose to
2. Open Filename2 and verify that the formula in cell 1 (in Filename3)
still looks like it is suppose to.
3. Save Filename3 and verify that the formula in cell 1 still looks like it
is suppose to. THIS IS WHERE I FAIL! My formula now looks like this:
=[apple.xlsx]Sheet1!$A1-[apple.xls]Sheet1!$A1

It seems that Excel 2007 cannot save external links with the same name even
if they are in different directories. I can create two name ranges (ie.
AppleAlloc and AppleAlloc2) and it works but that would mean over 360 names
in my real life application.

Excel 2003 handles all this well - is this a known Excel 2007 problem OR
maybe it is a configuration issue?

Any ideas, thoughts would greatly be appreciated. We recently upgraded and
I am tasked with getting many of our 2003 files to work in 2007.

Thanks

To the best of my experience, no version of Excel has ever done this
well. Because it is a Windows problem (unable to identify files
individually, won't use the unique path universally), not entirely
Excel. Although, Excel can't even do it properly on a Mac, which
doesn't have the Windows problem.

The solution is to change the names. Add project initials, a date,
something.