Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying formulas without changing the range
I use a lot of Countif formulas. When I copy the formula from one worksheet
to another, or from one row to another, it changes the range (i.e. J6:J69 becomes J7:J70, etc.) I want to maintain the same range (J6:J69) but cannot get it to do that. Does anyone know how I can keep the range the same in subsequent rows? I just want to change the criteria of the count, not the range. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying formulas without changing the range
Make the criteria range absolute.
Three ways to do this In the formula box select J6, and press <F4 It will change to $J$6. Now select J69, and repeat, so that it changes to $J$69. In the Formula box, Type "$"'s before J, 6, J and 69 Select the range J6:J69, and give it a range name. To do this, after selecting the range, click in the address box - it will show J6 - and type in a name, such as CritRange, and press <Enter Especially in other sheets, the latter will work well. You can now cpy this formula to your heart's content, and it will always refer to CritRange, or then to $J$6:$J$69! -- Hth Kassie Kasselman Change xxx to hotmail "gaelicamethyst" wrote: I use a lot of Countif formulas. When I copy the formula from one worksheet to another, or from one row to another, it changes the range (i.e. J6:J69 becomes J7:J70, etc.) I want to maintain the same range (J6:J69) but cannot get it to do that. Does anyone know how I can keep the range the same in subsequent rows? I just want to change the criteria of the count, not the range. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying formulas without changing the range
Use absolute addressing, as in:
=countif($J$6:$J$69,"something") The $ tells Excel not to adjust the address on copying. Regards, Fred. "gaelicamethyst" wrote in message ... I use a lot of Countif formulas. When I copy the formula from one worksheet to another, or from one row to another, it changes the range (i.e. J6:J69 becomes J7:J70, etc.) I want to maintain the same range (J6:J69) but cannot get it to do that. Does anyone know how I can keep the range the same in subsequent rows? I just want to change the criteria of the count, not the range. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying formulas and changing cell references | Excel Discussion (Misc queries) | |||
copying formulas but changing the column # | Excel Discussion (Misc queries) | |||
COPYING OFFSET FORMULA WITHOUT THE DATA RANGE CHANGING-DESPERATE | Excel Worksheet Functions | |||
Copying a named range with a changing cell reference | New Users to Excel | |||
Copying formulas without changing previous results/calculations | Excel Worksheet Functions |