Vlookup formula keeps changing. (2007)

[veespike]

I am working on creating a purchase order template, and I am using Vlookup to gather part descriptions from a part number.

The lookup I am using is this:
=IF(F25=0,"",VLOOKUP(F25,'f:\dir\dir\filename.xlsm'!parts,2,FALSE))

The file is on a networked drive. I intend for more than one location to be able to use the template, so they all need to have access to the parts table. The lookup works as written.

The problem I have is that excel is changing the formula to the following for some reason:
=IF(F25=0,"",VLOOKUP(F25,'filename.xlsm'!parts,2,FALSE))

This causes the formula to fail and fires an "undefined or non-rectangular error" when the template loads. If I then open the parts file on my desktop, the lookup works and I get my descriptions.

I have no idea what is causing the formula to change, and numerous attempts to change the cells back to the original formula are not working. There are other lookups doing similar things for vendor information that do not cause this issue. It only occurs with the parts lookup.

What is causing the formula to change?

 

[Smitty]

See if this helps any:

Daily Dose of Excel » Blog Archive » INDIRECT and closed workbooks

 

[veespike]

I believe I have solved my problem, so I thought I should follow up. Mostly because my solution indicates that excel does strange things with formulas in certain situations.

The sheet with the data is located on a networked drive, as I mentioned. But the template I was creating was on my local drive. When I moved the template file to the same location as the data file, the problem with the changing formulas cleared up. The formulas in the cells now showed the full path to the file, as I had entered it, rather than just the file name. So excel is changing file paths based on location, for some reason??

After that, I had the issue with not reading the data if the data file was not open. However, this was inconsistent, as sometimes it would work. And the other lookups that I have in the template worked fine. But those refer to a different file, one that is older. So I thought maybe the date file itself was the problem. The file was an .xlsm file. I changed it to an .xls (97-2003) and now it appears to work properly. So Excel will not read data from a macro enabled file if it is not open?