Problems with INDIRECT

chrisguk

Board Regular
Joined
Jan 10, 2011
Messages
128
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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

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


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
128
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,133,532
Messages
5,659,368
Members
418,499
Latest member
mbcmel

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