# VLOOKUP on a closed Workbook...

#### How_Do_I

##### Well-known Member
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...

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

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### p45cal

##### Well-known Member
=VLOOKUP(\$F5,INDIRECT("'"&A3&B3&".xls'!&D3"),2,FALSE)
becomes:
=VLOOKUP(\$F5,INDIRECT("'"&A3&B3&".xls'!"&D3),2,FALSE)
? Untested.

#### RobExcel

##### Board Regular
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
No, P45cal that didn't get it... Rob yours didn't work either...

Last edited:

#### RoryA

##### MrExcel MVP, Moderator

INDIRECT will not work with a closed workbook.

#### Jon von der Heyden

##### MrExcel MVP, Moderator
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

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

##### Board Regular
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
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...

Replies
19
Views
621
Replies
0
Views
473
Replies
3
Views
525
Replies
3
Views
301
Replies
5
Views
148

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,598
Messages
5,832,646
Members
430,151
Latest member
Kacbear

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back