Cell references change when entering new data

  • Thread starter Thread starter virfir97
  • Start date Start date
V

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
 
To lock A1:C20 using indirect you can use

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


Regards,

Peo Sjoblom
 
Back
Top