Prevent formula reference from changing

S

ScottyC

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
 
T

T. Valko

Try this:

=COUNTIF(INDIRECT("Sheet1!B2:B500"),"aprv")

This will always refer to Sheet1B2:B500

Biff
 
Top