Formula keeps altering by itself!

[Julie P.]

Hi, I am using the following Excel formula:

=COUNTA(C4:C3593)

It is placed in cell C3.

However, each night, I delete the cells from C4 to C1045, and elect to shift
left.

Then the formula all of a sudden becomes:

=COUNTA(C1046:C3593)

So is there any way to prevent it from changing like this? I want the
formula to always be:

=COUNTA(C4:C3593)

Cells C4 to C1045 need to be counted, even if they were just shifted from
the right column.

Thanks for any help!

Julie

 

[Debra Dalgleish]

Add an INDIRECT function: =COUNTA(INDIRECT("C4:C3593"))

 

[Julie P.]

Add an INDIRECT function: =COUNTA(INDIRECT("C4:C3593"))

Thanks Debra! I will give this a try!

J.

 

[CarlosAntenna]

Julie,

You could try just clearing the contents of the range of cells instead of
deleting them.

Select the range then press the delete button or right click and "clear
contents"

Carlos