VLOOKUP on a closed Workbook...

[How_Do_I]

What am I doing wrong with the attached please?

F4 and G4 are working... I change F4 and I get the expected result in G4 from my closed workbook...

My problem is building my VLOOKUP in F5 using CONCATENATE AND INDIRECT...

Any advice please?

Excel Workbook
	A	B	C	D	E	F	G
1						1	24
2	Folder:	File Name:	Sheet Name:	Named Range			2
3	C:\Users\Gus\Desktop\A\V Examples\	x VLOOKUP TEST Workbook	Tables	FullTable		P	Team
4						14	Graafschap
5						14	#REF!
Sheet1

 

[p45cal]

=VLOOKUP($F5,INDIRECT("'"&A3&B3&".xls'!&D3"),2,FALSE)
becomes:
=VLOOKUP($F5,INDIRECT("'"&A3&B3&".xls'!"&D3),2,FALSE)
? Untested.

 

[RobExcel]

You seem to have misplaced some quotes in G5, try:
G5 =VLOOKUP($F5,INDIRECT("'"&A3&B3&".xls'!"&D3),2,FALSE)

 

[How_Do_I]

No, P45cal that didn't get it... Rob yours didn't work either...

 

[RoryA]

INDIRECT will not work with a closed workbook.

 

[Jon von der Heyden]

For alternatives see this blog post.

Although generally I don't think referencing external workbooks is a good idea and should only be used when absolutely necessary. Consider pulling in the data from the external workbook (using VBA, or a query table).

 

[How_Do_I]

I've put some of the above solutions in a test workbook as I've tried them... I've just open the closed workbook and funny old thing, my sheet is now infested with the expected value...!

Got to go now but I'll read the alternative to INDIRECT on a closed workbook ASAP...

Thanks to all that have helped with this...

 

[RobExcel]

Right. I should have remembered that indirects don't work on closed workbooks. I always add a macro that runs when the workbook opens that warns the user that formulas won't calculate properly with the source workbook closed and then prompts them to ask if the macro should open the workbook.

 

[How_Do_I]

Everything is fine now Rob... The link that Jon gave me said that INDIRECT.EXT won't work either but I tried it and it does for me...