Problems with INDIRECT

chrisguk

Board Regular
Joined
Jan 10, 2011
Messages
135
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
Joined
May 10, 2007
Messages
463
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
Joined
Jan 10, 2011
Messages
135
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.
 

chrisguk

Board Regular
Joined
Jan 10, 2011
Messages
135


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
Joined
Jan 10, 2011
Messages
135
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)
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top