# VLookup and Indirect

#### jim may

##### Well-known Member
In Cell B29 #REF! is currently displayed, Can someone assist?
Excel Workbook
BCDEFGH
26Record:Path:FileName:SheetName:
27LOCK FREQUENCYC:\Users\James\Documents\ExcelChest\TestSheet1
28
29#REF!<<< Help With Formula !!
Sheet1
Excel 2007
Cell Formulas
RangeFormula
B29=VLOOKUP(\$B27,INDIRECT("[" & D27 & G27 & ".xls]" & "!" &H27 & "MyStuff"),2,FALSE)

My Sheet1 in my Test.xls File (Currently OPEN) shows:
Excel Workbook
AB
1DRIVER SEAT100.00
2LOCK FREQUENCY200.00
4LOCK LATCH400.00
5RECEPTACLE500.00
Sheet1
Excel 2007

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### Andrew Poulsom

##### MrExcel MVP
What would the formula look without INDIRECT?

#### jim may

##### Well-known Member
B29 Should return 200

#### Andrew Poulsom

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

##### Well-known Member
OK, sorry but this works...

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

#### Andrew Poulsom

##### MrExcel MVP
What would that be if you close test.xls?

#### jim may

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

##### MrExcel MVP
Try:

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

#### jim may

##### Well-known Member
Thanks Andrew - I got it!!

Replies
1
Views
681
Replies
3
Views
463
Replies
7
Views
1K
Replies
2
Views
582
Replies
3
Views
1K

1,191,384
Messages
5,986,311
Members
440,017
Latest member
vasanrajeswaran

### 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