Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Static Cell Refrences in Formulas
I have a formula that is looking at a range of cells on a second worksheet
in a workbook. The cell range is A2:A1000, the formula is looking at the range, and counting the number of times a certain value appears. =COUNTIF(Sheet2!A2:A1000,"THING") There are currently 116 different values in this cell range that I need statistics on, what I want to do is create one forumla and copy it 115 times WITHOUT the cell numbers changing in relation to where the formula is copied. For example, if I copy the formula from A1 to D20 on any given worksheet, the formula will be changed to: =COUNTIF(Sheet2!D21:D1019,"THING") I know that I will have to edit each formula to change the search criteria, but I don't want to have to spend the time changing the cell numbers on top of the value to be counted, or creating a formula from scratch for each value. Can someone help me? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Static Cell Refrences in Formulas
You can do both.
In A1:An, list your criteria. In B1, add =COUNTIF(Sheet2!$A$2:$A$1000,$A1) and copy down -- HTH RP (remove nothere from the email address if mailing direct) "Ben" wrote in message ... I have a formula that is looking at a range of cells on a second worksheet in a workbook. The cell range is A2:A1000, the formula is looking at the range, and counting the number of times a certain value appears. =COUNTIF(Sheet2!A2:A1000,"THING") There are currently 116 different values in this cell range that I need statistics on, what I want to do is create one forumla and copy it 115 times WITHOUT the cell numbers changing in relation to where the formula is copied. For example, if I copy the formula from A1 to D20 on any given worksheet, the formula will be changed to: =COUNTIF(Sheet2!D21:D1019,"THING") I know that I will have to edit each formula to change the search criteria, but I don't want to have to spend the time changing the cell numbers on top of the value to be counted, or creating a formula from scratch for each value. Can someone help me? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Static Cell Refrences in Formulas
Ben
=COUNTIF(Sheet2!$A$2:$A$1000,"THING") Surround the references with the $ signs to lock the column and rows. A1 is relative $A1 is column absolute A$1 is row absolute $A$1 is row and column absolute F2 to edit first formula then select the address and hit F4 to toggle through the options. Drag/copy that formula down 116 rows. There may be a way to get the search criteria into each cell without editing each formula. Do you have the 116 "Things" in a list somewhere that could be addressed. i.e. they are in a list in column B from B1:B116 In that case try this =COUNTIF(Sheet2!$A$2:$A$1000,B1) Drag/copy down 116 rows. B1 will increment to follow your list. Gord Dibben Excel MVP On Thu, 15 Dec 2005 17:46:45 -0600, "Ben" wrote: I have a formula that is looking at a range of cells on a second worksheet in a workbook. The cell range is A2:A1000, the formula is looking at the range, and counting the number of times a certain value appears. =COUNTIF(Sheet2!A2:A1000,"THING") There are currently 116 different values in this cell range that I need statistics on, what I want to do is create one forumla and copy it 115 times WITHOUT the cell numbers changing in relation to where the formula is copied. For example, if I copy the formula from A1 to D20 on any given worksheet, the formula will be changed to: =COUNTIF(Sheet2!D21:D1019,"THING") I know that I will have to edit each formula to change the search criteria, but I don't want to have to spend the time changing the cell numbers on top of the value to be counted, or creating a formula from scratch for each value. Can someone help me? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Static Cell Refrences in Formulas
Thank you for you help!
"Bob Phillips" wrote in message ... You can do both. In A1:An, list your criteria. In B1, add =COUNTIF(Sheet2!$A$2:$A$1000,$A1) and copy down -- HTH RP (remove nothere from the email address if mailing direct) "Ben" wrote in message ... I have a formula that is looking at a range of cells on a second worksheet in a workbook. The cell range is A2:A1000, the formula is looking at the range, and counting the number of times a certain value appears. =COUNTIF(Sheet2!A2:A1000,"THING") There are currently 116 different values in this cell range that I need statistics on, what I want to do is create one forumla and copy it 115 times WITHOUT the cell numbers changing in relation to where the formula is copied. For example, if I copy the formula from A1 to D20 on any given worksheet, the formula will be changed to: =COUNTIF(Sheet2!D21:D1019,"THING") I know that I will have to edit each formula to change the search criteria, but I don't want to have to spend the time changing the cell numbers on top of the value to be counted, or creating a formula from scratch for each value. Can someone help me? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Static Cell Refrences in Formulas
Thank you for your help!
"Gord Dibben" <gorddibbATshawDOTca wrote in message ... Ben =COUNTIF(Sheet2!$A$2:$A$1000,"THING") Surround the references with the $ signs to lock the column and rows. A1 is relative $A1 is column absolute A$1 is row absolute $A$1 is row and column absolute F2 to edit first formula then select the address and hit F4 to toggle through the options. Drag/copy that formula down 116 rows. There may be a way to get the search criteria into each cell without editing each formula. Do you have the 116 "Things" in a list somewhere that could be addressed. i.e. they are in a list in column B from B1:B116 In that case try this =COUNTIF(Sheet2!$A$2:$A$1000,B1) Drag/copy down 116 rows. B1 will increment to follow your list. Gord Dibben Excel MVP On Thu, 15 Dec 2005 17:46:45 -0600, "Ben" wrote: I have a formula that is looking at a range of cells on a second worksheet in a workbook. The cell range is A2:A1000, the formula is looking at the range, and counting the number of times a certain value appears. =COUNTIF(Sheet2!A2:A1000,"THING") There are currently 116 different values in this cell range that I need statistics on, what I want to do is create one forumla and copy it 115 times WITHOUT the cell numbers changing in relation to where the formula is copied. For example, if I copy the formula from A1 to D20 on any given worksheet, the formula will be changed to: =COUNTIF(Sheet2!D21:D1019,"THING") I know that I will have to edit each formula to change the search criteria, but I don't want to have to spend the time changing the cell numbers on top of the value to be counted, or creating a formula from scratch for each value. Can someone help me? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with formulas changing cell reference | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Combine two formulas in one cell | Excel Discussion (Misc queries) | |||
How do I protect cell formulas used for daily counts? | Excel Worksheet Functions | |||
Hyperlinks static but formulas are not. Why? | Excel Discussion (Misc queries) |