Countif - reference a sheet via a cell

[dips_007]

Hi All

Need some help how would i reference a sheet via a cell, i want to replace 'Sheet-A' in the formula with a reference to A2. I can reference the criteria but not the range.

=COUNTIF('Sheet-A'!A:A,B2)


SHEET 1 called Summary
Sheet Colour
Sheet-A Red
Sheet-B Green
Sheet-C Yellow
Sheet-D Blue
Sheet-E White
Sheet-A Blue
Sheet-B White
Sheet-C Red
Sheet-D Green
Sheet-E Yellow


SHEET2 called Sheet-A
Colour
Red
Green
Yellow
Blue
White
Blue
White
Red
Green
Yellow

[zbor]

tRY WITH indirect FUNCTION:

=COUNTIF(INDIRECT("'Sheet-"&A2&"'!A:A"), b2)

[Ace_XL]

Try..

=COUNTIF(INDIRECT("'"&A2&"'!A:A"),B2)

where A2 has Sheet-A

[zbor]

Note:
ACE_XL referencing to Sheet-A while I referencing to just A.
Otherwise are same.

[dips_007]

Thanks guys, never used indirect before will come in handy.

[dips_007]

How would i go about using Indirect if i had a "summary" sheet and the sheet name was in cell A2 for all references to Sheet-B.

=SUMIFS('Sheet-B'!B:B,'Sheet-B'!A:A,"Finance",'Sheet-B'!C:C,"Rejected")

[Ace_XL]

Same logic

=SUMIFS(INDIRECT("'"&A2&"'!B:B"),INDIRECT("'"&A2&"'!A:A"),"Finance",INDIRECT("'"&A2&"'!C:C"),"Rejected")