I'm trying to understand why conditional formatting formulas adjust
their address references differently then the same formuals in cells. In
short,
Excel is resetting row references to row 1 when a new row is inserted.
This is not how the same formulas (but in cells) are adjusted when rows
are inserted. ....HELP....

Example....
Assume the contents of G30 = 5

The following three conditional format equations

=SUM(G$35:G35)>=G$30
=SUM(G$35:G35)>=G30
=SUM(G$35:G35)>=5

Adjust differently when a new row 32 is inserted. When
I insert this row....

EXCEL automatically changes the first two equation to

=SUM(G$1:G35)>=G$30 and
=SUM(G$1:G35)>=G30

While the third equation adjusts to what I want, (and see when
the same formulas are used in a normal cell).

=SUM(G$36:G36)>=G$30

Can anyone explain why EXCEL is doing this?

I do not get the changes that you indicate in Excel 2002,
please give exact steps to recreate the problem.

What values do you have in cells in G35 before inserting
a row and what value do you have in G36 after inserting row.
What is the active cell address and the selection range
when you entered the conditional formula.,
What cell are you looking at the conditional formula of
and what cell are you looking at in the conditional formula of
in after you have inserted a new row between 31 and 32

What value do you have in Cell G30 before and after
inserting row between rows 31 and 32.
What do have for conditional formula in the cell that
had been the active cell when you entered the C.F.

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Allenx" <(E-Mail Removed)> wrote in message news:vyYJh.12786$(E-Mail Removed)...
> I'm trying to understand why conditional formatting formulas adjust
> their address references differently then the same formuals in cells. In
> short,
> Excel is resetting row references to row 1 when a new row is inserted.
> This is not how the same formulas (but in cells) are adjusted when rows
> are inserted. ....HELP....
>
> Example....
> Assume the contents of G30 = 5
>
> The following three conditional format equations
>
> =SUM(G$35:G35)>=G$30
> =SUM(G$35:G35)>=G30
> =SUM(G$35:G35)>=5
>
> Adjust differently when a new row 32 is inserted. When
> I insert this row....
>
> EXCEL automatically changes the first two equation to
>
> =SUM(G$1:G35)>=G$30 and
> =SUM(G$1:G35)>=G30
>
> While the third equation adjusts to what I want, (and see when
> the same formulas are used in a normal cell).
>
> =SUM(G$36:G36)>=G$30
>
> Can anyone explain why EXCEL is doing this?
>
>
>
>

Thank you for helping.

I'm running Excel 2000 on XP.

New empty spreadsheet except.....
Cell G30 contains number 5
Cells G35, 36, 37, 38, 39, and 40 each contain number 1

I insert a Conditioning format for G35 via FORMULA

=SUM(G$35:G35)<= G$30
(green background if true)

I copy this CF with the FORMAT PAINTER to cells G36 - G40
I examine the conditional formula in G36. It correctly shows

=SUM(G$35:G36)<=G$30

I check the other cells. All CF formulas are as expected.
The green background is correctly seen on all but G40 (this is as expected).

Now, I insert a new row in row 32.

The numeric 1 all shift down one row. Cell G35 moves to cell G36, etc.
When I examine the CF formula for cell G36 I discover

=SUM(G$1:G$36)<=G$30 !!!!

Various other cases exist, but this should be easily repeatable.
Where did this formula come from ????

Thank you again for helping.




"David McRitchie" <(E-Mail Removed)> wrote in message
news:u$(E-Mail Removed)...
>I do not get the changes that you indicate in Excel 2002,
> please give exact steps to recreate the problem.
>
> What values do you have in cells in G35 before inserting
> a row and what value do you have in G36 after inserting row.
> What is the active cell address and the selection range
> when you entered the conditional formula.,
> What cell are you looking at the conditional formula of
> and what cell are you looking at in the conditional formula of
> in after you have inserted a new row between 31 and 32
>
> What value do you have in Cell G30 before and after
> inserting row between rows 31 and 32.
> What do have for conditional formula in the cell that
> had been the active cell when you entered the C.F.
>
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "Allenx" <(E-Mail Removed)> wrote in message
> news:vyYJh.12786$(E-Mail Removed)...
>> I'm trying to understand why conditional formatting formulas adjust
>> their address references differently then the same formuals in cells. In
>> short,
>> Excel is resetting row references to row 1 when a new row is inserted.
>> This is not how the same formulas (but in cells) are adjusted when rows
>> are inserted. ....HELP....
>>
>> Example....
>> Assume the contents of G30 = 5
>>
>> The following three conditional format equations
>>
>> =SUM(G$35:G35)>=G$30
>> =SUM(G$35:G35)>=G30
>> =SUM(G$35:G35)>=5
>>
>> Adjust differently when a new row 32 is inserted. When
>> I insert this row....
>>
>> EXCEL automatically changes the first two equation to
>>
>> =SUM(G$1:G35)>=G$30 and
>> =SUM(G$1:G35)>=G30
>>
>> While the third equation adjusts to what I want, (and see when
>> the same formulas are used in a normal cell).
>>
>> =SUM(G$36:G36)>=G$30
>>
>> Can anyone explain why EXCEL is doing this?
>>
>>
>>
>>
>
>

It would be a lot more efficient to use conditional
format on the cells you want formatted ahead of time
normally I would format the entire column, but I don't
know that I have referred to a single cell in a different
row. I'll have to look at this tomorrow if you don't get
an answer tonight. Did you try a constant instead of $G30
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Allenx" <(E-Mail Removed)> wrote in message news:LC_Jh.12755$(E-Mail Removed)...
> Thank you for helping.
>
> I'm running Excel 2000 on XP.
>
> New empty spreadsheet except.....
> Cell G30 contains number 5
> Cells G35, 36, 37, 38, 39, and 40 each contain number 1
>
> I insert a Conditioning format for G35 via FORMULA
>
> =SUM(G$35:G35)<= G$30
> (green background if true)
>
> I copy this CF with the FORMAT PAINTER to cells G36 - G40
> I examine the conditional formula in G36. It correctly shows
>
> =SUM(G$35:G36)<=G$30
>
> I check the other cells. All CF formulas are as expected.
> The green background is correctly seen on all but G40 (this is as expected).
>
> Now, I insert a new row in row 32.
>
> The numeric 1 all shift down one row. Cell G35 moves to cell G36, etc.
> When I examine the CF formula for cell G36 I discover
>
> =SUM(G$1:G$36)<=G$30 !!!!
>
> Various other cases exist, but this should be easily repeatable.
> Where did this formula come from ????
>
> Thank you again for helping.
>
>
>
>
> "David McRitchie" <(E-Mail Removed)> wrote in message
> news:u$(E-Mail Removed)...
> >I do not get the changes that you indicate in Excel 2002,
> > please give exact steps to recreate the problem.
> >
> > What values do you have in cells in G35 before inserting
> > a row and what value do you have in G36 after inserting row.
> > What is the active cell address and the selection range
> > when you entered the conditional formula.,
> > What cell are you looking at the conditional formula of
> > and what cell are you looking at in the conditional formula of
> > in after you have inserted a new row between 31 and 32
> >
> > What value do you have in Cell G30 before and after
> > inserting row between rows 31 and 32.
> > What do have for conditional formula in the cell that
> > had been the active cell when you entered the C.F.
> >
> > ---
> > HTH,
> > David McRitchie, Microsoft MVP - Excel
> > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
> >
> > "Allenx" <(E-Mail Removed)> wrote in message
> > news:vyYJh.12786$(E-Mail Removed)...
> >> I'm trying to understand why conditional formatting formulas adjust
> >> their address references differently then the same formuals in cells. In
> >> short,
> >> Excel is resetting row references to row 1 when a new row is inserted.
> >> This is not how the same formulas (but in cells) are adjusted when rows
> >> are inserted. ....HELP....
> >>
> >> Example....
> >> Assume the contents of G30 = 5
> >>
> >> The following three conditional format equations
> >>
> >> =SUM(G$35:G35)>=G$30
> >> =SUM(G$35:G35)>=G30
> >> =SUM(G$35:G35)>=5
> >>
> >> Adjust differently when a new row 32 is inserted. When
> >> I insert this row....
> >>
> >> EXCEL automatically changes the first two equation to
> >>
> >> =SUM(G$1:G35)>=G$30 and
> >> =SUM(G$1:G35)>=G30
> >>
> >> While the third equation adjusts to what I want, (and see when
> >> the same formulas are used in a normal cell).
> >>
> >> =SUM(G$36:G36)>=G$30
> >>
> >> Can anyone explain why EXCEL is doing this?
> >>
> >>
> >>
> >>
> >
> >
>
>

Thank you again. Yes I did try a constant and numerous
other ideas,,,,,,

To repeat, the formula

=SUM(G$35:G35)<=G$30 gets corrupted after a row 32 is inserted

yet if the formula were rewritten as

=SUM(G$35:G35)<=5 and "format painted" down the cells, this works fine
before and after I insert the row,

and, if I DEFINE the name "BASE" to be cell G35

=SUM(Base:G35)<=G$30 works fine too.


The CF formula in my actual application is considerably more complicated
then the above, but it is this anomoly that is causing the problems.






"David McRitchie" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> It would be a lot more efficient to use conditional
> format on the cells you want formatted ahead of time
> normally I would format the entire column, but I don't
> know that I have referred to a single cell in a different
> row. I'll have to look at this tomorrow if you don't get
> an answer tonight. Did you try a constant instead of $G30
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "Allenx" <(E-Mail Removed)> wrote in message
> news:LC_Jh.12755$(E-Mail Removed)...
>> Thank you for helping.
>>
>> I'm running Excel 2000 on XP.
>>
>> New empty spreadsheet except.....
>> Cell G30 contains number 5
>> Cells G35, 36, 37, 38, 39, and 40 each contain number 1
>>
>> I insert a Conditioning format for G35 via FORMULA
>>
>> =SUM(G$35:G35)<= G$30
>> (green background if true)
>>
>> I copy this CF with the FORMAT PAINTER to cells G36 - G40
>> I examine the conditional formula in G36. It correctly shows
>>
>> =SUM(G$35:G36)<=G$30
>>
>> I check the other cells. All CF formulas are as expected.
>> The green background is correctly seen on all but G40 (this is as
>> expected).
>>
>> Now, I insert a new row in row 32.
>>
>> The numeric 1 all shift down one row. Cell G35 moves to cell G36, etc.
>> When I examine the CF formula for cell G36 I discover
>>
>> =SUM(G$1:G$36)<=G$30 !!!!
>>
>> Various other cases exist, but this should be easily repeatable.
>> Where did this formula come from ????
>>
>> Thank you again for helping.
>>
>>
>>
>>
>> "David McRitchie" <(E-Mail Removed)> wrote in message
>> news:u$(E-Mail Removed)...
>> >I do not get the changes that you indicate in Excel 2002,
>> > please give exact steps to recreate the problem.
>> >
>> > What values do you have in cells in G35 before inserting
>> > a row and what value do you have in G36 after inserting row.
>> > What is the active cell address and the selection range
>> > when you entered the conditional formula.,
>> > What cell are you looking at the conditional formula of
>> > and what cell are you looking at in the conditional formula of
>> > in after you have inserted a new row between 31 and 32
>> >
>> > What value do you have in Cell G30 before and after
>> > inserting row between rows 31 and 32.
>> > What do have for conditional formula in the cell that
>> > had been the active cell when you entered the C.F.
>> >
>> > ---
>> > HTH,
>> > David McRitchie, Microsoft MVP - Excel
>> > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
>> > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>> >
>> > "Allenx" <(E-Mail Removed)> wrote in message
>> > news:vyYJh.12786$(E-Mail Removed)...
>> >> I'm trying to understand why conditional formatting formulas adjust
>> >> their address references differently then the same formuals in cells.
>> >> In
>> >> short,
>> >> Excel is resetting row references to row 1 when a new row is inserted.
>> >> This is not how the same formulas (but in cells) are adjusted when
>> >> rows
>> >> are inserted. ....HELP....
>> >>
>> >> Example....
>> >> Assume the contents of G30 = 5
>> >>
>> >> The following three conditional format equations
>> >>
>> >> =SUM(G$35:G35)>=G$30
>> >> =SUM(G$35:G35)>=G30
>> >> =SUM(G$35:G35)>=5
>> >>
>> >> Adjust differently when a new row 32 is inserted. When
>> >> I insert this row....
>> >>
>> >> EXCEL automatically changes the first two equation to
>> >>
>> >> =SUM(G$1:G35)>=G$30 and
>> >> =SUM(G$1:G35)>=G30
>> >>
>> >> While the third equation adjusts to what I want, (and see when
>> >> the same formulas are used in a normal cell).
>> >>
>> >> =SUM(G$36:G36)>=G$30
>> >>
>> >> Can anyone explain why EXCEL is doing this?
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>