vlookup with external file changes to startup dir (Excel 97 SR2)

[holtkamp]

I have a problem. We have a complex sheet, which will be used by a number of people who get a local copy.

We want to use a VLookup function to retrieve default numbers from a Excel Sheet located on the network.

The problem is that when we type in location of this central file and we open the file part of the path is substituted with the startup directory of the user. Does anyone know how to prevent this?

Thnx in advance

[Wassim]

<img src=/S/hello.gif border=0 alt=hello width=25 height=29> holtkamp

You are telling me that a forumla is changing everytime you start working with the workbook?

That is odd!!!

Try a VBA solution that resets all the formulas to what they need to be.

Something that would replace the path portion that is changing with what it needs to be.

HTH

Wassim

[rory]

This is not a problem I've come across before but have you tried typing the filepath into a cell on the worksheet and using the indirect function in your vlookup functions? (or is this what you're currently doing?)

[rory]

One other thought - does this still happen if you open the referenced file before you open the workbook that references it?

[holtkamp]

Thanks to all that replied. Although the solutions you provide would have helped I found another one. On the microsoft site. I had searched there but had limited my search to Excel 97.

When I tried it again (forgetting XL97) it came up with this solution
XL2000: Link Path Changes to Xlstart Folder (Q214361).

Dennis Holtkamp<img src=/S/flags/Netherlands.gif border=0 alt=Netherlands width=30 height=18>