Problems with INDIRECT

[chrisguk]

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?

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

 

[Canapone]

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.

 

[chrisguk]

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]

anyone?

 

[chrisguk]

anyone?

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 .

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

 

[chrisguk]

okay i figured that out too with the following solution:

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