Formula changes on own accord

[Guest]

Here is one of the many formulas in my worksheet: =COUNTIF(L15:L103,">0")

When I first set that formula up, I put L15:L200. There are 12 worksheets
(one for each month), and one month might have 189 rows, another month 153
rows, another one 195, and then another one maybe only 98 rows. This is why
I set it at 200 because it never exceeds that, rather than changing it for
each worksheet. However, it seems that after I save the file, the formula
changes. How do I make the formula stay at L15:L200 regardless that there
are only 103 rows filled?

Connie Martin

 

[JulieD]

Hi Connie

not sure why your formulas are changing - i've never seen excel do this
before ... just to confirm
you type
=COUNTIF(L15:L200,">0")
into a cell outside of this range and close & save the workbook - when you
open it and check the formula it has changed to a smaller range?

if so, does it do this on a new blank workbook? are you running any code
anywhere in the workbook? what version of excel are you using?

Cheers
JulieD

 

[Guest]

Hi Julie,

I can't be 100% sure when the formula changes. I know that I have grouped
the sheets and changed the formula on all 12 at the same time, that everyone
would show the range 15:200 in all formulas. But now I see that February's
sheet had changed to 103.

I am using Excel 2000. I don't know what running codes is. There are no
macros in the file. In another similar file the range was 28:600, and it all
changed to 589. I just changed that all back to 600, saved it and closed it.
Re-opened and it's still at 600. So, I don't know what gives. I just know
that I have set these formulas so that I never have to change them. I put
the range well beyond what would ever be required so that they always capture
all the data for the month. However, when I looked at my summary sheet and a
chart I thought, "That can't be!" So, I went back to the individual sheets
and because the formula had changed to a smaller number they were no longer
capturing all the data and therefore the summary sheet and chart were not
true. Strange!

Connie

 

[JulieD]

Hi Connie

strange indeed ... can't think what would cause it ... hopefully someone
else might have some ideas.

Cheers
JulieD

 

[Guest]

If any of the rows in the data section were deleted, the formula would change.

Just an idea...

 

[Guest]

That could very well be what has happened. I will watch for this the next
time that occurs. No way to lock the formula in?

Connie Martin

 

[Guest]

How about =COUNTIF(A:A,">")?

That could very well be what has happened. I will watch for this the next
time that occurs. No way to lock the formula in?

Connie Martin

 

[Guest]

That's interesting. It's works in this formula, but it won't always work
because in some columns there are numbers in rows 1-14 that I don't want to
include. That's why my formula starts at row 15. Anyway, I think my
question has been answered as to why my formula keeps changing. I do believe
it has to do with deleting rows.

Thank you for your help.
Connie Martin