Countif - refer to another sheet

[DarrenWood]

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

 

[Guest]

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)

 

[DarrenWood]

Thats excellent Kevin , very much appreciated - worked first time

Can you just clarify what you meant about the space in the sheet name.
How does that alter the formula you gave. Is it the gap between the &
and the A2 you are talking about ? This will help me understand it
better.

One last thing, what difference do the $ signs make ?

Omce again many thanks

Darren

 

[Guest]

Can you just clarify what you meant about the space in the sheet name.

How does that alter the formula you gave. Is it the gap between the &
and the A2 you are talking about ? This will help me understand it
better.

The example you gave me did not have a space in the sheet name, jimmy. But
the formula I gave you allowed for the possibility of a space. This was done
using ' (single quotes) inside of double quotes. Try entering a simple
formula: Type = and then point to a tab (worksheet) and selecting cell a1 in
the other spreadsheet. Next do the same thing, but with a worksheet that
contains a space in its name and notice the difference:
Sheet1!A1
'jimmy sheet'!A1
Note that Excel inserted a single quote around the worksheet with the space
in its name. I used the formula:
=COUNTIF(INDIRECT("'" & A2 & "'!$G$10:$G$203"),A12)
The & is the concatenation function, which basically means join 2 things
together.
"a" & "b" would give you ab whereas a1 & " " & a2 would give you the
contents of Ebb Tide if a1 contained "Ebb" and a2 contained "Tide"

Had I not accounted for the possibility of a space, I could have used:
=COUNTIF(INDIRECT(A2 & "!$G$10:$G$203"),A12)

One last thing, what difference do the $ signs make ?

As for the $, that is used to make a cell reference absolute. You can use
relative cell referencing as in A2 in the above formula. Then when you copy
the formula down, the row numbers will automatically change to reflect the
new rows you are on. Copy the formula to A3 for instance, and it becomes:
=COUNTIF(INDIRECT(A3 & "!$G$10:$G$203"),A13)
However, notice that the range G10:G203 did not change. That is because
with the addition of the $ preceding either the row or column, that makes the
referecne absolute. You are telling Excel that you want to use those
particular cells no matter where you copy your formula to.
You can also make the references mixed where either the row is absolute or
the column is absolute but the other is relative.
$A1 means no matter what column I copy to I still want to use the value in
column A, but the row number will change accordingly.
and A$1 means no matter what row I copy to, I still want to reference row1,
but the column letter will change accordingly.

HTH.