VLOOKUP on a closed Workbook...

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,783
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFG
1124
2Folder:File Name:Sheet Name:Named Range2
3C:\Users\Gus\Desktop\A\V Examples\x VLOOKUP TEST WorkbookTablesFullTablePTeam
414Graafschap
514#REF!
Sheet1
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
=VLOOKUP($F5,INDIRECT("'"&A3&B3&".xls'!&D3"),2,FALSE)
becomes:
=VLOOKUP($F5,INDIRECT("'"&A3&B3&".xls'!"&D3),2,FALSE)
? Untested.
 

RobExcel

Board Regular
Joined
Jan 6, 2010
Messages
69
You seem to have misplaced some quotes in G5, try:
G5 =VLOOKUP($F5,INDIRECT("'"&A3&B3&".xls'!"&D3),2,FALSE)
 

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,783
Office Version
  1. 2010
Platform
  1. Windows
No, P45cal that didn't get it... Rob yours didn't work either...
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,483
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

INDIRECT will not work with a closed workbook.
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,801
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 23, 2009
Messages
1,783
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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...! :LOL:

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

Thanks to all that have helped with this...:)
 

RobExcel

Board Regular
Joined
Jan 6, 2010
Messages
69
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

Well-known Member
Joined
Oct 23, 2009
Messages
1,783
Office Version
  1. 2010
Platform
  1. Windows
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,108,510
Messages
5,523,318
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top