Conditional formatting formula behavior

[Allenx]

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?

 

[David McRitchie]

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.

 

[Allenx]

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]

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

 

[Allenx]

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.