Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Rijswijk, Netherlands
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

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

    <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
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

    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?)
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

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

    Microsoft MVP - Excel

  5. #5
    New Lounger
    Join Date
    Jan 2001
    Location
    Rijswijk, Netherlands
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •