| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Prevent formula reference from changing
Hi,
=countif(sheet1!"$b$2;$b$500,"=aprv") On this formula, I need to stop the$b$2 reference from automatically changing when I insert a column before the B column (ie; no matter how many columns are inserted, I want whatever ends up in the B column to be the focus of the countif function. Either that, or is there just a simple way to have the formula look at the entire worksheet and pull those values out (as they will be unique)? Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Prevent formula reference from changing
Try this:
=COUNTIF(INDIRECT("Sheet1!B2:B500"),"aprv") This will always refer to Sheet1B2:B500 Biff "ScottyC" wrote in message ... Hi, =countif(sheet1!"$b$2;$b$500,"=aprv") On this formula, I need to stop the$b$2 reference from automatically changing when I insert a column before the B column (ie; no matter how many columns are inserted, I want whatever ends up in the B column to be the focus of the countif function. Either that, or is there just a simple way to have the formula look at the entire worksheet and pull those values out (as they will be unique)? Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Prevent formula reference from changing
Thanks, Bif, you're wonderful!
"ScottyC" wrote: Hi, =countif(sheet1!"$b$2;$b$500,"=aprv") On this formula, I need to stop the$b$2 reference from automatically changing when I insert a column before the B column (ie; no matter how many columns are inserted, I want whatever ends up in the B column to be the focus of the countif function. Either that, or is there just a simple way to have the formula look at the entire worksheet and pull those values out (as they will be unique)? Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Prevent formula reference from changing
You're welcome. Thanks for the feedback!
Biff "ScottyC" wrote in message ... Thanks, Bif, you're wonderful! "ScottyC" wrote: Hi, =countif(sheet1!"$b$2;$b$500,"=aprv") On this formula, I need to stop the$b$2 reference from automatically changing when I insert a column before the B column (ie; no matter how many columns are inserted, I want whatever ends up in the B column to be the focus of the countif function. Either that, or is there just a simple way to have the formula look at the entire worksheet and pull those values out (as they will be unique)? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is this possible with excel ? | Excel Discussion (Misc queries) | |||
| Formula that references another reference | Excel Worksheet Functions | |||
| Changing a cell reference within a formula w/another cell | Excel Worksheet Functions | |||
| Worksheet name / reference as a formula? | Excel Discussion (Misc queries) | |||
| HOW DO I COPY FORMULA WITHOUT CHANGING CELL REFERENCE | Excel Discussion (Misc queries) | |||
Linear Mode
