dynamic filename in a vlookup? Help!

BritinExile

New member
Joined
May 23, 2014
Messages
1
Reaction score
0
Points
0
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)


ABCD
1Stage1Stage2Stage3
2ProjAprojA Stage1.xlsprojA Stage2.xlsprojA Stage3.xls
3ProjBprojB Stage1.xls......
4ProjC.........


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

AB
1
2ProjAA2



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 :happy:
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 
Top