Iferror and indirect Help

[renewgeorgia123]

Hi,

I want to pull info from othersheets.


Currently I use this and it works:

=IFERROR(INDEX('NOV17'!$J:$J,MATCH('DEC17'!$B16,'NOV17'!$B:$B,0)),"")

and another for each month eg

=IFERROR(INDEX('Oct17'!$I:$I,MATCH('DEC17'!$B16,'Oct17'!$B:$B,0)),"")


But I have to redo it every month and add the next.

So.. I was thinking of using indirect for DEC 17, NOV17 , OCT 17 etc are all names of the sheets as well as the name of the column where I put the data.

AT1 is the cell where I have the name of the sheet so I tried to do part of the equation here:

=IFERROR(INDEX(INDIRECT('AT1'!$J:$J),MATCH('DEC17'!$B2,'NOV17'!$B:$B,0)),"")


I want to remove all the DEC NOV OCT etc... and just have indirects so each column just refers to the name of the sheet in Row 1.

Can someone help me please do this with indirect?

Thanks,
Neal

 

[StephenCrump]

Welcome to the Forum!

Try: =IFERROR(INDEX(INDIRECT("'"&AT1&"'!$J:$J"),MATCH('Dec17'!$B16,INDIRECT("'"&AT1&"'!$B:$B"),0)),"")

where AT1 contains the string value 'Nov17, for example.

Alternatively, if the current month is December 2017, and the cell A1 contains the date 1 December 2017 (which you can format MMMYY to display as Dec17) then you can generate the previous months' sheet names using formulae, e.g. to return the cell A1 from the Nov17 sheet: =INDIRECT(TEXT(EDATE(A1,-1),"mmmyy")&"!A1")

i.e. generate on the fly, rather than storing previous months' dates in cells AT1 etc.

 

[renewgeorgia123]

Thank you!

=IFERROR(INDEX(INDIRECT("'"&AT1&"'!$J:$J"),MATCH('Dec17'!$B16,INDIRECT("'"&AT1&"'!$B:$B"),0)),"")

Um.. one more thing. How do I replace the DEC17 in the middle now? I relocated that one to a cell AK1 so AK1 is Dec17 and I need to reference B2.

Thank you

 

[StephenCrump]

Try replacing 'Dec17'!$B16 with INDIRECT("'"&AK1&"'!$B16")

You might have noticed that I have wrapped all the sheet references in single quotes, which allows for the possibility that the sheet name contains space characters.

If your sheet names will never have spaces, e.g. Dec17, Nov17 etc, then you can also use the slightly simpler INDIRECT(AK1&"!$B16")

 

[renewgeorgia123]

That worked.... but one other thing now.

This works but when I copy the cell down to row 3, row 4 etc... The d2 stays the same... and it needs to change to d3 d4 etc. I do not have $ in front of d2, so I am not sure why it is not changing as I copy it down.


=IFERROR(INDEX(INDIRECT("'"&$BI$1&"'!$h:$h"),MATCH(INDIRECT("'"&$B$1&"'!d2"),INDIRECT("'"&$BI$1&"'!$b:$b"),0)),"")

any idea why?

Thanks again.

 

[StephenCrump]

In this formula: INDIRECT("'"&$B$1&"'!d2") the D2 is wrapped in quotes, therefore doesn't change.

To make the D2 a relative reference, use:

=INDIRECT("'"&$B$1&"'!"&CELL("address",D2))

This is not foolproof, because we're relying on D2 on the formula sheet not being moved, e.g. by inserting rows or columns.