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

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

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

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