Results 1 to 2 of 2

Thread: dynamic filename in a vlookup? Help!

  1. #1

    dynamic filename in a vlookup? Help!

    Register for a FREE account, and/
    or Log in to avoid these ads!


    I have a table of vlookups that reference other files on a network drive, something like

    =VLOOKUP("",'G:\abc\def\[PROJECTA STAGE1.xls]Sheet1'!...

    My table looks something like this, with 3 stages across and many projects down the rows:
    (I've added in the filename ref that the lookup needs)

    A B C D
    1 Stage1 Stage2 Stage3
    2 ProjA projA Stage1.xls projA Stage2.xls projA Stage3.xls
    3 ProjB projB Stage1.xls ... ...
    4 ProjC ... ... ...

    The vlookup works but I have to enter a filename in each cell. I'd like to make the filename dynamic, so that I can copy the formula across and down as new projects are added.

    Extra info:

    I tried using something like this, which does give the filename that I need, but does not work if I plug it into the lookup
    =CONCATENATE(INDIRECT("Sheet1!"&B2)," Stage1.xls")

    A B
    2 ProjA A2

    Exactly, the vlookup is:
    =VLOOKUP("",'G:\DSI\Teams\DSI\03_Testing\07 Test Defect Dashboard\May 14\[BIB2FA S1.xls]Sheet1'!$A$1:$F$7,MATCH(J$2,'G:\DSI\Teams\DSI\03_Testing\07 Test Defect Dashboard\May 14\[BIB2FA S1.xls]Sheet1'!$A$1:$F$1,0),0)


  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Mississauga, Canada
    Excel Version
    Excel 2016
    The INDIRECT is the function you would need to do this, but unfortunately, that function does not work with closed workbooks.

    This is a VBA user defined function developed called PULL() by Harlan Grove that would work, but could be a slow process.

Posting Permissions

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