Assuming your sheet name is in cell A2, try:

=COUNTIF(INDIRECT("'" & A2 & "'!$G$10:$G$203"),A12)

Note: I accounted for a space in the sheet name although your example did
not have one (just in case you want to adapt it later for a sheet name with a
space in it)

--
Kevin Vaughn


"DarrenWood" wrote:


I am using the countif function and it currently reads

=countif('jimmy'!g10:g203,a12)

jimmy refers to the name of the sheet the formula works on

I want to remove the name 'jimmy' from the formula and instead replace
it with a cell reference refering to a cell on the same sheet as the
formula. So for instance cell B50 has the word 'jimmy' in there.

The result would be exactly the same however it would mean i could just
change the B50 cell data from say jimmy to fred and that would inturn
change the worksheet that the formula is looking in.

Sounds easy but i cant work it out.

Any help would be really appreciated

Darren


--
DarrenWood
------------------------------------------------------------------------
DarrenWood's Profile: http://www.excelforum.com/member.php...o&userid=16460
View this thread: http://www.excelforum.com/showthread...hreadid=510703