Use sheet names in column in COUNTIF formula?

[Ed from AZ]

I have the names of my worksheets in Col A. I want to do a COUNTIF
for all these sheets in the same range on every sheet. Is there some
way I can incorporate the cell with the sheet name into the formula?
Something like:

Col A Col B
Sheet1 =COUNTIF('(ValueofThisSheet!A2)'!B2:B20,"*")
Sheet2
Sheet3

And then of course copy it down the column to pick up all the sheet
names?

Ed

 

[Pete_UK]

Hi Ed,

you use the INDIRECT function to do this, like so:

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

Then copy down.

Hope this helps.

Pete

 

[Ed from AZ]

Thanks, Pete! It worked great!
Ed

Hi Ed,

you use the INDIRECT function to do this, like so:

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

Then copy down.

Hope this helps.

Pete






- Show quoted text -

 

[Pete_UK]

You're welcome, Ed - thanks for feeding back.

Pete