Cell references change when entering new data

[[email protected]]

"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

[Peo Sjoblom]

To lock A1:C20 using indirect you can use

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


Regards,

Peo Sjoblom



"[email protected]" wrote:

> "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
>
>

[Duke Carey]

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

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

& copy it down



"[email protected]" wrote:

> "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
>
>