Results 1 to 5 of 5
  1. #1
    Join Date
    May 2011
    Posts
    47

    Using variable file names in a vLookup

    Does anyone have an example of using a variable file name (and tab) in a vLookup? I have many files with many vLookups that have to be changed every month. It would be wonderful if I could only key the variable name once instead of copying and pasting so many vLookups. I've tried a number of different ways to accomplish this but have been unsuccessful. Please, if anyone has an example that works?
    Jack Kent

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,275
    That got the gary matter thinking

    trick use
    =INDIRECT()

    and build the a cell up and them point the INDIRECT to it and it should work
    Last edited by myle; 12-04-12 at 03:47. Reason: spelling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE

  3. #3
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    163
    Here's an example of the indirect function that Myle mentioned.

    Joshua
    Attached Files Attached Files

  4. #4
    Join Date
    May 2011
    Posts
    47

    Using variable file names in a vLookup

    Thank you both. I got this to work for one of the arguments but not the other. It works great with:

    =VLOOKUP(B3,INDIRECT("'["&C16&".xlsx]"&D16&"'!$B$1:$D$5"),[file2.xlsx]Sheet1!$D$1,FALSE)

    but if I use the same format on the third argument:

    =VLOOKUP(B3,INDIRECT("'["&C16&".xlsx]"&D16&"'!$B$1:$D$5"),"'["&C16&".xlsx]"&D16&"'!$B$1,FALSE")

    I get a #Value error message.

    Any thoughts would be appreciated.

    Jack Kent

  5. #5
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    163
    It could be a number of things. I'd check in the following order:

    1. Check workbook name to see that it is the same as C16 with neither having trailing spaces. Make sure it is an .xlsx and not an .xls

    2. Check the worksheet/tab for the same along with D16

    3. Other than that, I'd check your quote marks and concatenation. Excel can be finicky with that stuff.

Posting Permissions

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