Same problem here. We have a template to calculate prices, containing various vlookups that lookup prices in pricelists.
Vlookup formulas refer to pricelists with UNC names.
The most extreme I'm seeing is the following:
=vlookup(x,'\\server\share\a\b\pricelists\pricelistA.xlsx!PriceTable',4,false) is in a sheet that is itself stored on \\server\share.
Just copy this excel sheet to a local drive. Suddenly, the formula says:
Huh? We COPIED the (closed) file, and someone/something decides that the lookup files are somewhere else?
I know one thing for certain: when all the above is done with Excel 2007 running on an XP machine, the sheet can be placed anywhere without getting the vlookup changed. Even if some users have the '\\server\share\ mapped to a drive letter and other
don't, this is not a problem. It seems that WHEN the share is mapped to a drive letter, Excel displays the vlookup in 'drive letter' format, but the formula is not really changed. When opened again on a machine without drive mapping, the UNC reappears.
However, opening the sheet from Excel 2007 or Excel 2010 on a Windows 7 machine and saving it again (without touching the vlookup formula) strange things happen. In some cases \\server\share\a\b\ changes into \\server\share\b\ leaving out the first directory
The 'edit links' function suddenly show multiple lines referring to the price list, with different directory paths, where one path was shown before. The variation being in different directory path levels suddenly missing.
This problem is now becoming a real pain in the neck, as with have several thousands of these calculation sheets, that each can become crippled if someone dares to change anything in the sheet and resaving it.
I have now spent all afternoon experimenting, and I'm beginning to doubt that Excel is the real culprit. The fact that all is well on Windows XP working with the same server files makes me think that Windows 7 has something to do with it.
I've read somewhere that some work has been done in Windows 7 (maybe already in Vista, but I'm glad we skipped that one) to make sure that links to removable media (like USB drives) are not made worthless when such drives are assigned a different drive letter.
Could it be that Windows is mistakenly trying to repair something that isn't broken? And could it be that the effect is not (yet) too bad when using Excel 2007 with Windows 7, but that it gets much worse when Excel 2010 and Windows 7 are combining efforts
to make our lives miserable?
I recognize what TBS Consultants describe that sometimes all lookup formulas show '#N/A', but that 'updating' a single formula by changing nothing makes all #N/A's going away.
Hope these observations help us to find out whats happening here.
To Wendall (MSFT), if you're still here: if you haven't been able to reproduce this, bad luck. But the problem exists for sure, you bet.
I hope this will help a little in unraveling this mistery and helping many people and organisations out of their misery.