VLookup and Indirect

[jim may]

In Cell B29 #REF! is currently displayed, Can someone assist?

Excel Workbook
	B	C	D	E	F	G	H
26	Record:		Path:			FileName:	SheetName:
27	LOCK FREQUENCY		C:\Users\James\Documents\ExcelChest\			Test	Sheet1
28
29	#REF!	<<< Help With Formula !!
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B29	=VLOOKUP($B27,INDIRECT("[" & D27 & G27 & ".xls]" & "!" &H27 & "MyStuff"),2,FALSE)

My Sheet1 in my Test.xls File (Currently OPEN) shows:

Excel Workbook
	A	B
1	DRIVER SEAT	100.00
2	LOCK FREQUENCY	200.00
3	HEADLAMP CLEANING	300.00
4	LOCK LATCH	400.00
5	RECEPTACLE	500.00
Sheet1

Excel 2007

 

[Andrew Poulsom]

What would the formula look without INDIRECT?

 

[jim may]

B29 Should return 200

 

[Andrew Poulsom]

You haven't answered my question. Enter the formula normally and post it. Then we'll be able to build it with INDIRECT.

 

[jim may]

OK, sorry but this works...

=VLOOKUP(B27,test.xls!MyStuff,2,FALSE)

 

[Andrew Poulsom]

What would that be if you close test.xls?

 

[jim may]

I CLOSED Test.xls and the below formula still Display the 200.

=VLOOKUP(B27,'C:\Users\James\Documents\ExcelChest\test.xls'!MyStuff,2,FALSE)

 

[Andrew Poulsom]

Try:

=VLOOKUP($B27,INDIRECT("'"&D27&G27&".xls'!MyStuff"),2,FALSE)

 

[jim may]

Thanks Andrew - I got it!!