How to reference a cell with a sheet name in a formula

[VXNQST]

I am trying to replace the sheet name by referencing a cell with a sheet name in it. In my example below,

In my example below, I thought the answer would be:

=SUMIF("A2"!A:A,"1","A2"!B:B) but this seems to not work.

My goal is to be able to drag down and have the formula change based on the new sheet that I will add.

Please help, I have gone through the Q&A and it seems INDIRECT is what I need, but I cannot get it to work.

 

[jasonb75]

The correct way to do it would be

=SUMIF(INDIRECT("'"&A2&"'!A:A"),1,INDIRECT("'"&A2&"'!B:B"))

 

[VXNQST]

The correct way to do it would be

=SUMIF(INDIRECT("'"&A2&"'!A:A"),1,INDIRECT("'"&A2&"'!B:B"))

Thanks for the response. However, I am getting a #REF! when I type in that formula. Any help will be appreciated!

 

[Fluff]

From that image it looks as though sheet Data1 does not have a space before the 1, but A2 does.

 

[VXNQST]

From that image it looks as though sheet Data1 does not have a space before the 1, but A2 does.

Good call! Thanks!

 

[Fluff]

Glad we could help & thanks for the feedback.