# Problems with INDIRECT

#### chrisguk

##### Board Regular
I am trying to pull the value of a cell in Sheet Jan by using indirect. Cell A1 is a dropdown list with months therefore pulling the value from any given sheet. My IF(OR( statement is incomplete because I want to add more logical elements to it.

But when I use this formula it returns #REF! and tries to open another workbook. Can someone assist?

Code:
``=IF(OR(INDIRECT("'"&A1&"'!"&"D4")<>"");VLOOKUP('A1'!A4;'A1'!A:C;1;FALSE);2)``

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### Canapone

##### Active Member
Hi,

a first step : try to use INDIRECT for arguments of VLOOKUP

=IF(OR(INDIRECT("'"&A1&"'!"&"D4")<>"");VLOOKUP(indirect("'"&A1&"'!A4");indirect("'"&A1\$"'!A:C");1;0);2)

I hope it's of some help.

Last edited:

#### chrisguk

##### Board Regular
Hi,

a first step : try to use INDIRECT for arguments of VLOOKUP

=IF(OR(INDIRECT("'"&A1&"'!"&"D4")<>"");VLOOKUP(indirect("'"&A1&"'!A4");indirect("'"&A1\$"'!A:C");1;0);2)

I hope it's of some help.

Sorry,
it didnt work. Something about parathesis in the wrong place I think.

anyone?

#### chrisguk

##### Board Regular

okay
I have this partially working. The only issue is that where you see cell ref D4 and A4 these should change to D5 and A5 respectively as you move down each row. This is not happening but I will try to figure it out while I wait for some assistance .

Code:
``=IF(OR(INDIRECT("'"&A\$1&"'!"&"D4")<>"");VLOOKUP(INDIRECT("'"&A\$1&"'!"&"A4");INDIRECT("'"&A\$1&"'!"&"A:C");1;0);2)``

#### chrisguk

##### Board Regular
okay i figured that out too with the following solution:

Code:
``=IF(OR(INDIRECT("'"&A\$1&"'!"&CELL("address";D4))<>"");VLOOKUP(INDIRECT("'"&A\$1&"'!A4");INDIRECT("'"&A\$1&"'!A:C");1;0);2)``

Replies
1
Views
478
Replies
3
Views
791
Replies
3
Views
192
Replies
0
Views
391
Replies
8
Views
266

1,172,134
Messages
5,879,235
Members
433,412
Latest member
diakaoma

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