Formula keeps altering by itself!

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

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

Julie P. wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

"Debra Dalgleish" <[email protected]> wrote in message
news:[email protected]...
> Add an INDIRECT function: =COUNTA(INDIRECT("C4:C3593"))
>


Thanks Debra! I will give this a try!

J.

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

"Julie P." <[email protected]> wrote in message
news:[email protected]...
> 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
>