countif from tab dependent on cell value

[kingy2]

Hi

I'm trying to use countif to count if cell C3 in Sheet 6 is equal to cell C2

=COUNTIF(Sheet6!C3,C2)

But I want to replace 'Sheet6!' with a cell reference so that whatever is in that cell will direct the queery to that tab, e.g.

=COUNTIF('A1'!C3,C2)

So if cell A1=Sheet6 it will use Sheet6 C3 as the range, but if I change A1 to Sheet7 it will use sheet 7 C3. I can't seem to get the syntax correct.

I hope I've explained that correctly

[Andrew-R]

Try this ...

Please Login or Register  to view this content.

[DonkeyOte]

When using INDIRECT for sheet references it's generally a good idea to encase sheet reference within ' ' just in case...

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

remember too that you can use R1C1 notation with INDIRECT should you wish to adapt target reference across a range for ex.

[kingy2]

Simples eh?

Thanks very much

Sorry, only used this site once and it was a while ago, do I do something to say thanks and mark thread as solved?

[Andrew-R]

Yes, please mark the thread as solved, and if you'd like to thank DonkeyOte or myself then click on the scales icon at the top right of our posts.