# Thread: dynamic filename in a vlookup? Help!

1. ## dynamic filename in a vlookup? Help!

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. 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
•