Using Indirect in an offset index formula

[Chrican]

Hi all, I've got a working offset index function to return a value from a specific tab right now, but I need to go one step further and use indirect to dynamically reference tabs, as there' are about 200 of them and this would be much quicker than find+replace. I can get the indirect function to work for just a basic sum, as follows (note, this is not what I wanted but rather just to check if it worked re:formatting etc):

=SUM(INDIRECT(A4&"!B11:B12"))

But now I need to work just the indirect part, not the sum, into this function:

=IF(ISNA(OFFSET(INDEX('6204'!4:4,1,MATCH($B$2,'6204'!4:4,0)),-2,3,)),0,OFFSET(INDEX('6204'!4:4,1,MATCH($B$2,'6204'!4:4,0)),-2,3,))

where the tab is 6204, and that number is referenced from A4. How do I do this? It might be helpful if somebody could explain the syntax too, in that the only part I'm getting lost on here is the ", ', and & that all the google results bring up but do differently.

 

[Jonmo1]

A few things...

1. to deal with spaces in sheet names, you need apostraphes around the sheet name.
using your first example, it would be like this
=SUM(INDIRECT("'"&A4&"'!B11:B12"))

2. When testing for errors, you only need to test the part that causes the error, not the whole formula..
In your formula, it is only the MATCH(...) that causes the error.
So you only have to test that.
This makes formula shorter, and more efficient (less calculations)

so your formula could be..
=IF(ISNA(MATCH($B$2,'6204'!4:4,0)),0,OFFSET(INDEX('6204'!4:4,1,MATCH($B$2,'6204'!4:4,0)),-2,3,))

3. You don't need offset here...Since you're offsetting by -2 Rows, and 3 columns..
You can do that within the index itself..

=IF(ISNA(MATCH($B$2,'6204'!4:4,0)),0,INDEX('6204'!2:2,1,MATCH($B$2,'6204'!4:4,0)+3))


And finally, to inpliment the indirect for the sheet name..
Replace all instances of '6204'!2:2 and '6204'!4:4
with
INDIRECT("'"&A4&"'!2:2") and INDIRECT("'"&A4&"'!4:4")


so it becomes
=IF(ISNA(MATCH($B$2,INDIRECT("'"&A4&"'!4:4"),0)),0,INDIRECT("'"&A4&"'!2:2"),1,MATCH($B$2,INDIRECT("'"&A4&"'!4:4"),0)+3))


Hope that helps..

 

[Chrican]

That was perfect! You saved me so much time I might just get out of here early today, thank you sir

 

[Jonmo1]

Glad to help, thanks for the feedback.