Cell references change when entering new data

[virfir97]

"X-No-Archive: yes"

In cell E1, I have the formula =COUNTIF($A1:$C20,$D$1)>5

If I enter new data in A:C the cell references in the formula change. I
will be autofilling the formula in column E, so the relative references
$A1:$C20 will have to change. ie

Cell E2 =COUNTIF($A2:$C21,$D$1)>5

How do I use INDIRECT or an alternative function so that the cell
references do not change when entering data. The simplest formula
adaptation please, since I will be using the methodology in numerous
complex/nested formulas.

Thanks

 

[Guest]

To lock A1:C20 using indirect you can use

=COUNTIF(INDIRECT("$A1:$C20"),$D$1)>5


Regards,

Peo Sjoblom

 

[Guest]

for E1, replace your $A1:$c20 reference with

OFFSET(D1,0,-3,20,3)

& copy it down