Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif - refer to another sheet
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif - refer to another sheet
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif - refer to another sheet
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 -- DarrenWood ------------------------------------------------------------------------ DarrenWood's Profile: http://www.excelforum.com/member.php...o&userid=16460 View this thread: http://www.excelforum.com/showthread...hreadid=510703 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif - refer to another sheet
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. -- Kevin Vaughn "DarrenWood" wrote: Thats excellent Kevin , very much appreciated - worked first time Omce again many thanks Darren -- DarrenWood ------------------------------------------------------------------------ DarrenWood's Profile: http://www.excelforum.com/member.php...o&userid=16460 View this thread: http://www.excelforum.com/showthread...hreadid=510703 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using CountIf with criteria on another sheet | Excel Worksheet Functions | |||
How to get a row consecutive from a sheet to another | Excel Worksheet Functions | |||
Using CountIf with criteria on another sheet? | Excel Worksheet Functions | |||
Using a relative SHEET reference for source data in a chart | Charts and Charting in Excel | |||
data from one sheet to several in sequential order! | New Users to Excel |