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