Dynamic Sheet referencing

[RSCexcel]

I have this formula that sums a column if it matches “dog” on column A on a sheet named “1.”

=SUMIF('1'!$a:$a,”dog”,'1'!$b:$b)

Is there a way to automate changing that sheet to “2,” then "3," ... for the next cells?

i.e.
=SUMIF('2’!$a:$a,”dog”,'2'!$b:$b)
=SUMIF('3’!$a:$a,”dog”,'3'!$b:$b)

and so on…

Thanks.

 

[Barrie Davidson]

Assuming your sheet reference is in cell A1, try

=SUMIF(INDIRECT("'"&A1&"'!$a:$a"),"dog",INDIRECT("'"&A1&"'!$b:$b"))

Regards,

 

[RSCexcel]

That worked great Barrie. Thanks.

BTW, can I use the "INDIRECT" function for a text string as well?

i.e. from the example I used, can I do something like this to place the sheet reference after the text "dog?"

=SUMIF(INDIRECT("'"&A1&"'!$a:$a"),"dog&INDIRECT("'"&A1&"'"),INDIRECT("'"&A1&"'!$b:$b"))

thanks...

 

[Barrie Davidson]

That worked great Barrie. Thanks.

BTW, can I use the "INDIRECT" function for a text string as well?

i.e. from the example I used, can I do something like this to place the sheet reference after the text "dog?"

=SUMIF(INDIRECT("'"&A1&"'!$a:$a"),"dog&INDIRECT("'"&A1&"'"),INDIRECT("'"&A1&"'!$b:$b"))

thanks...

I'm not clear on what you need the sheet reference for. Would you be searching for "dog1"? If that's the case, just use

=SUMIF(INDIRECT("'"&A1&"'!$a:$a"),"dog"&A1,INDIRECT("'"&A1&"'!$b:$b"))

Am I on the right track here?

 

[RSCexcel]

I actually want to append "1" after the text "dog" and before another text (ex. "cat").
Therefore, the search will be "dog 1 cat", then "dog 2 cat" etc...
Where "1" and "2" are the sheet/tab names.
Sorry for the confusion.
Thanks.

 

[Barrie Davidson]

I actually want to append "1" after the text "dog" and before another text (ex. "cat").
Therefore, the search will be "dog 1 cat", then "dog 2 cat" etc...
Where "1" and "2" are the sheet/tab names.
Sorry for the confusion.
Thanks.

Okay, assuming you'll have a space between the three:

=SUMIF(INDIRECT("'"&A1&"'!$a:$a"),"dog "&A1&" cat",INDIRECT("'"&A1&"'!$b:$b"))

Now we're there, right? :wink:

 

[RSCexcel]

Perfect!
Thanks Barrie