dynamic filename in a vlookup? Help!

[BritinExile]

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

 

[NBVC]

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.