Formula changes on own accord

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

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

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

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

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

Hi Connie

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

Cheers
JulieD

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

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

Just an idea...

"JulieD" wrote:

> Hi Connie
>
> strange indeed ... can't think what would cause it ... hopefully someone
> else might have some ideas.
>
> Cheers
> JulieD
>
> "Connie Martin" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> >> 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
> >>
> >> "Connie Martin" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >>
> >>
> >>
>
>
>

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

"JBoulton" wrote:

> If any of the rows in the data section were deleted, the formula would change.
>
> Just an idea...
>
> "JulieD" wrote:
>
> > Hi Connie
> >
> > strange indeed ... can't think what would cause it ... hopefully someone
> > else might have some ideas.
> >
> > Cheers
> > JulieD
> >
> > "Connie Martin" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" wrote:
> > >
> > >> 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
> > >>
> > >> "Connie Martin" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> > 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
> > >>
> > >>
> > >>
> >
> >
> >

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



"Connie Martin" wrote:

> 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
>
> "JBoulton" wrote:
>
> > If any of the rows in the data section were deleted, the formula would change.
> >
> > Just an idea...
> >
> > "JulieD" wrote:
> >
> > > Hi Connie
> > >
> > > strange indeed ... can't think what would cause it ... hopefully someone
> > > else might have some ideas.
> > >
> > > Cheers
> > > JulieD
> > >
> > > "Connie Martin" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" wrote:
> > > >
> > > >> 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
> > > >>
> > > >> "Connie Martin" <[email protected]> wrote in message
> > > >> news:[email protected]...
> > > >> > 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
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >

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

"JBoulton" wrote:

> How about =COUNTIF(A:A,">")?
>
>
>
> "Connie Martin" wrote:
>
> > 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
> >
> > "JBoulton" wrote:
> >
> > > If any of the rows in the data section were deleted, the formula would change.
> > >
> > > Just an idea...
> > >
> > > "JulieD" wrote:
> > >
> > > > Hi Connie
> > > >
> > > > strange indeed ... can't think what would cause it ... hopefully someone
> > > > else might have some ideas.
> > > >
> > > > Cheers
> > > > JulieD
> > > >
> > > > "Connie Martin" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" wrote:
> > > > >
> > > > >> 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
> > > > >>
> > > > >> "Connie Martin" <[email protected]> wrote in message
> > > > >> news:[email protected]...
> > > > >> > 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
> > > > >>
> > > > >>
> > > > >>
> > > >
> > > >
> > > >