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!

    Hi,

    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
    1
    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)


    thanks

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    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
  •