Excel 2003 bug

  • Thread starter Thread starter Shawn Casey
  • Start date Start date
S

Shawn Casey

This is a bug in all versions of Excel.

When using conditional formatting with more than more formula, the
formatting ceases to go through all conditions once one condition becomes
true.

This isn't the same behavior as when cell values are used. If this is the
intended behavior, there should at least be an option to continue through
all conditions.
 
This is a bug in all versions of Excel.

When using conditional formatting with more than more formula, the
formatting ceases to go through all conditions once one condition becomes
true.

This isn't the same behavior as when cell values are used. If this is the
intended behavior, there should at least be an option to continue through
all conditions.
Click to expand...

I've never seen the behavior you claim to occur when using cell values. Could
you please post a specific example?

CF has always worked for me as documented, whether I use cell values or
formulas.


--ron
 
Shawn,

Conditional formatting will apply formats for only one of the three
conditions. It has no choice if you have formats that are exclusive (like
red for one condition, green for another, etc.). But I suspect you've set
up formats that are not exclusive (like red for one condition, bold for
another) where you'd like to have them both applied when both their
conditions are TRUE. But since it applies format(s) for only one of the
three conditions, it stops after finding the first of the three formulas
that resolves to TRUE (thus prioritizing them for the case where more than
one condition is satisfied -- it uses the first condition that is TRUE).
Remember that formats from any or all of the three tabs (font, border, and
patterns) can be applied for each condition.

If you want to apply multiple formats, each with their own condition, I
suspect you'll have to do that with a macro.

Or this the condition where I've TOTALLY missed the point?

Earl Kiosterud
mvpearl omitthisword at verizon period net
 
Thanks for looking at this Earl, you are right, this is what i'm trying to
do. It sure would be nice if it was an option on the conditional formatting
to not stop on first true, but continue down the list applying what
satisfies the condition.

Now there seems to be two other bugs that I've found.


1. Using indirect addressing within the AND statement for conditional
formatting doesn't work.

example:
if the conditional forrmating is:
formula is: =(INDIRECT(ADDRESS(ROW(),COLUMN()-1))<>"")
this evaluates to true but if the conditional formatting is:
formula is: =AND((INDIRECT(ADDRESS(ROW(),COLUMN()-1))<>""), TRUE )
this doesn't evaluate to true.

Of course, i'm using something more than TRUE, but this reproducer fails to
work properly.

I got around this problem by using r1c1 style references and the formula:
formula is: =AND(rc[-1]<>"", TRUE)
evaluates to true correctly.

Using this caused me to find the next bug:

2. Inserting a row modifies unpredictably modifies the conditional
formatting.

I applied conditional formatting over three columns that uses r1c1 style
references as shown above. When I insert a row, the first column's
conditional formatting isn't global to the column any more because the
references around which i've inserted have been linked to the old cells,
causing them to be unique. What's weird is the other two columns are fine.
Here are the column conditional formatting rules, all have 3 formulas with
the first formatting the cell according to condition 2 and 3.

Column 1:
=AND(RC[-1]<>"", R[1]C<>RC)
=R[1]C<>RC
=RC<>""

Column 2:
=AND(R[1]C[-1]<>RC[-1],RC<>"")
=R[1]C[-1]<>RC[-1]
=(RC<>"")

Column 3:
=AND(RC[-1]<>"", R[1]C[-2]<>RC[-2])
= R[1]C[-2]<>RC[-2]
=RC[-1]<>""



It seems like the deeper I dig, the more holes I find.

Thanks,
Shawn
 
Shawn,

It appears you want to apply the color if the cell to the left is not empty.
The INDIRECT seems not to work inside an AND as you've said. Try this
instead:

=AND( B2<>"", TRUE)

Change the B2 to the address of the cell that's left of the active (white)
cell in your selection. It will be changed relatively (as with a copied
formula) for all cells in the selection, thus always examining the cell
that's one left of the one being conditionally formatted.

Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Shawn Casey said:
Thanks for looking at this Earl, you are right, this is what i'm trying to
do. It sure would be nice if it was an option on the conditional formatting
to not stop on first true, but continue down the list applying what
satisfies the condition.

Now there seems to be two other bugs that I've found.


1. Using indirect addressing within the AND statement for conditional
formatting doesn't work.

example:
if the conditional forrmating is:
formula is: =(INDIRECT(ADDRESS(ROW(),COLUMN()-1))<>"")
this evaluates to true but if the conditional formatting is:
formula is: =AND((INDIRECT(ADDRESS(ROW(),COLUMN()-1))<>""), TRUE )
this doesn't evaluate to true.

Of course, i'm using something more than TRUE, but this reproducer fails to
work properly.

I got around this problem by using r1c1 style references and the formula:
formula is: =AND(rc[-1]<>"", TRUE)
evaluates to true correctly.

Using this caused me to find the next bug:

2. Inserting a row modifies unpredictably modifies the conditional
formatting.

I applied conditional formatting over three columns that uses r1c1 style
references as shown above. When I insert a row, the first column's
conditional formatting isn't global to the column any more because the
references around which i've inserted have been linked to the old cells,
causing them to be unique. What's weird is the other two columns are fine.
Here are the column conditional formatting rules, all have 3 formulas with
the first formatting the cell according to condition 2 and 3.

Column 1:
=AND(RC[-1]<>"", R[1]C<>RC)
=R[1]C<>RC
=RC<>""

Column 2:
=AND(R[1]C[-1]<>RC[-1],RC<>"")
=R[1]C[-1]<>RC[-1]
=(RC<>"")

Column 3:
=AND(RC[-1]<>"", R[1]C[-2]<>RC[-2])
= R[1]C[-2]<>RC[-2]
=RC[-1]<>""



It seems like the deeper I dig, the more holes I find.

Thanks,
Shawn

Earl Kiosterud said:
Shawn,

Conditional formatting will apply formats for only one of the three
conditions. It has no choice if you have formats that are exclusive (like
red for one condition, green for another, etc.). But I suspect you've set
up formats that are not exclusive (like red for one condition, bold for
another) where you'd like to have them both applied when both their
conditions are TRUE. But since it applies format(s) for only one of the
three conditions, it stops after finding the first of the three formulas
that resolves to TRUE (thus prioritizing them for the case where more than
one condition is satisfied -- it uses the first condition that is TRUE).
Remember that formats from any or all of the three tabs (font, border, and
patterns) can be applied for each condition.

If you want to apply multiple formats, each with their own condition, I
suspect you'll have to do that with a macro.

Or this the condition where I've TOTALLY missed the point?

Earl Kiosterud
mvpearl omitthisword at verizon period net
Click to expand...
Click to expand...
 
That was a simple example of how their is a bug in Excel, but as shown in
the second bug example, the real scenario involves rows below the current
row. When I insert rows, the cell references become messed up using
relative addressing.


Earl Kiosterud said:
Shawn,

It appears you want to apply the color if the cell to the left is not empty.
The INDIRECT seems not to work inside an AND as you've said. Try this
instead:

=AND( B2<>"", TRUE)

Change the B2 to the address of the cell that's left of the active (white)
cell in your selection. It will be changed relatively (as with a copied
formula) for all cells in the selection, thus always examining the cell
that's one left of the one being conditionally formatted.

Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Shawn Casey said:
Thanks for looking at this Earl, you are right, this is what i'm trying to
do. It sure would be nice if it was an option on the conditional formatting
to not stop on first true, but continue down the list applying what
satisfies the condition.

Now there seems to be two other bugs that I've found.


1. Using indirect addressing within the AND statement for conditional
formatting doesn't work.

example:
if the conditional forrmating is:
formula is: =(INDIRECT(ADDRESS(ROW(),COLUMN()-1))<>"")
this evaluates to true but if the conditional formatting is:
formula is: =AND((INDIRECT(ADDRESS(ROW(),COLUMN()-1))<>""), TRUE )
this doesn't evaluate to true.

Of course, i'm using something more than TRUE, but this reproducer fails to
work properly.

I got around this problem by using r1c1 style references and the formula:
formula is: =AND(rc[-1]<>"", TRUE)
evaluates to true correctly.

Using this caused me to find the next bug:

2. Inserting a row modifies unpredictably modifies the conditional
formatting.

I applied conditional formatting over three columns that uses r1c1 style
references as shown above. When I insert a row, the first column's
conditional formatting isn't global to the column any more because the
references around which i've inserted have been linked to the old cells,
causing them to be unique. What's weird is the other two columns are fine.
Here are the column conditional formatting rules, all have 3 formulas with
the first formatting the cell according to condition 2 and 3.

Column 1:
=AND(RC[-1]<>"", R[1]C<>RC)
=R[1]C<>RC
=RC<>""

Column 2:
=AND(R[1]C[-1]<>RC[-1],RC<>"")
=R[1]C[-1]<>RC[-1]
=(RC<>"")

Column 3:
=AND(RC[-1]<>"", R[1]C[-2]<>RC[-2])
= R[1]C[-2]<>RC[-2]
=RC[-1]<>""



It seems like the deeper I dig, the more holes I find.

Thanks,
Shawn

Earl Kiosterud said:
Shawn,

Conditional formatting will apply formats for only one of the three
conditions. It has no choice if you have formats that are exclusive (like
red for one condition, green for another, etc.). But I suspect you've set
up formats that are not exclusive (like red for one condition, bold for
another) where you'd like to have them both applied when both their
conditions are TRUE. But since it applies format(s) for only one of the
three conditions, it stops after finding the first of the three formulas
that resolves to TRUE (thus prioritizing them for the case where more than
one condition is satisfied -- it uses the first condition that is TRUE).
Remember that formats from any or all of the three tabs (font, border, and
patterns) can be applied for each condition.

If you want to apply multiple formats, each with their own condition, I
suspect you'll have to do that with a macro.

Or this the condition where I've TOTALLY missed the point?

Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

This is a bug in all versions of Excel.

When using conditional formatting with more than more formula, the
formatting ceases to go through all conditions once one condition becomes
true.

This isn't the same behavior as when cell values are used. If this
Click to expand...
is
the
intended behavior, there should at least be an option to continue through
all conditions.
Click to expand...
Click to expand...
Click to expand...
 
Shawn,

Yeah. The RC reference style adjusts in the same way that the A1 style does
when you insert rows, or do anything that moves stuff.

You can refer to another row with the following, which seems to work in an
AND in conditional formatting. The following is for the cell immediately
below the active one. If rows are inserted, it continues to refer to the
cell below the active one.

=AND(OFFSET(B2,1,0,1,1)<>"", TRUE)

Again, adjust B2 to match the active (white) cell in your selection. The
formula is actually referring to itself, but doesn't activate a circular
reference raspberry, since there's an offset.

Is this what you need?
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Shawn Casey said:
That was a simple example of how their is a bug in Excel, but as shown in
the second bug example, the real scenario involves rows below the current
row. When I insert rows, the cell references become messed up using
relative addressing.


Earl Kiosterud said:
Shawn,

It appears you want to apply the color if the cell to the left is not empty.
The INDIRECT seems not to work inside an AND as you've said. Try this
instead:

=AND( B2<>"", TRUE)

Change the B2 to the address of the cell that's left of the active (white)
cell in your selection. It will be changed relatively (as with a copied
formula) for all cells in the selection, thus always examining the cell
that's one left of the one being conditionally formatted.

Earl Kiosterud
mvpearl omitthisword at verizon period net
Click to expand...
trying
to
do. It sure would be nice if it was an option on the conditional formatting
to not stop on first true, but continue down the list applying what
satisfies the condition.

Now there seems to be two other bugs that I've found.


1. Using indirect addressing within the AND statement for conditional
formatting doesn't work.

example:
if the conditional forrmating is:
formula is: =(INDIRECT(ADDRESS(ROW(),COLUMN()-1))<>"")
this evaluates to true but if the conditional formatting is:
formula is: =AND((INDIRECT(ADDRESS(ROW(),COLUMN()-1))<>""), TRUE )
this doesn't evaluate to true.

Of course, i'm using something more than TRUE, but this reproducer
Click to expand...
fails
to
work properly.

I got around this problem by using r1c1 style references and the formula:
formula is: =AND(rc[-1]<>"", TRUE)
evaluates to true correctly.

Using this caused me to find the next bug:

2. Inserting a row modifies unpredictably modifies the conditional
formatting.

I applied conditional formatting over three columns that uses r1c1 style
references as shown above. When I insert a row, the first column's
conditional formatting isn't global to the column any more because the
references around which i've inserted have been linked to the old cells,
causing them to be unique. What's weird is the other two columns are fine.
Here are the column conditional formatting rules, all have 3 formulas with
the first formatting the cell according to condition 2 and 3.

Column 1:
=AND(RC[-1]<>"", R[1]C<>RC)
=R[1]C<>RC
=RC<>""

Column 2:
=AND(R[1]C[-1]<>RC[-1],RC<>"")
=R[1]C[-1]<>RC[-1]
=(RC<>"")

Column 3:
=AND(RC[-1]<>"", R[1]C[-2]<>RC[-2])
= R[1]C[-2]<>RC[-2]
=RC[-1]<>""



It seems like the deeper I dig, the more holes I find.

Thanks,
Shawn

"Earl Kiosterud" <mvpearl omitthisword at verizon period net> wrote in
message Shawn,

Conditional formatting will apply formats for only one of the three
conditions. It has no choice if you have formats that are exclusive (like
red for one condition, green for another, etc.). But I suspect
Click to expand...
you've
set
up formats that are not exclusive (like red for one condition, bold for
another) where you'd like to have them both applied when both their
conditions are TRUE. But since it applies format(s) for only one of the
three conditions, it stops after finding the first of the three formulas
that resolves to TRUE (thus prioritizing them for the case where
Click to expand...
more
than
one condition is satisfied -- it uses the first condition that is TRUE).
Remember that formats from any or all of the three tabs (font,
Click to expand...
border,
and
patterns) can be applied for each condition.

If you want to apply multiple formats, each with their own
Click to expand...
Click to expand...
condition,
I
Click to expand...
Click to expand...
 
That is exactly what I needed (I used RC instead of B2 of course since I
applied it over the entire column).
Thanks for the work around to the bugs!

The weird thing about bug #2 is that the references were only messed up for
one of the columns...

Earl Kiosterud said:
Shawn,

Yeah. The RC reference style adjusts in the same way that the A1 style does
when you insert rows, or do anything that moves stuff.

You can refer to another row with the following, which seems to work in an
AND in conditional formatting. The following is for the cell immediately
below the active one. If rows are inserted, it continues to refer to the
cell below the active one.

=AND(OFFSET(B2,1,0,1,1)<>"", TRUE)

Again, adjust B2 to match the active (white) cell in your selection. The
formula is actually referring to itself, but doesn't activate a circular
reference raspberry, since there's an offset.

Is this what you need?
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Shawn Casey said:
That was a simple example of how their is a bug in Excel, but as shown in
the second bug example, the real scenario involves rows below the current
row. When I insert rows, the cell references become messed up using
relative addressing.


Earl Kiosterud said:
Shawn,

It appears you want to apply the color if the cell to the left is not empty.
The INDIRECT seems not to work inside an AND as you've said. Try this
instead:

=AND( B2<>"", TRUE)

Change the B2 to the address of the cell that's left of the active (white)
cell in your selection. It will be changed relatively (as with a copied
formula) for all cells in the selection, thus always examining the cell
that's one left of the one being conditionally formatted.

Earl Kiosterud
mvpearl omitthisword at verizon period net
Click to expand...
trying
to
do. It sure would be nice if it was an option on the conditional
formatting
to not stop on first true, but continue down the list applying what
satisfies the condition.

Now there seems to be two other bugs that I've found.


1. Using indirect addressing within the AND statement for conditional
formatting doesn't work.

example:
if the conditional forrmating is:
formula is: =(INDIRECT(ADDRESS(ROW(),COLUMN()-1))<>"")
this evaluates to true but if the conditional formatting is:
formula is: =AND((INDIRECT(ADDRESS(ROW(),COLUMN()-1))<>""), TRUE )
this doesn't evaluate to true.

Of course, i'm using something more than TRUE, but this reproducer fails
to
work properly.

I got around this problem by using r1c1 style references and the formula:
formula is: =AND(rc[-1]<>"", TRUE)
evaluates to true correctly.

Using this caused me to find the next bug:

2. Inserting a row modifies unpredictably modifies the conditional
formatting.

I applied conditional formatting over three columns that uses r1c1 style
references as shown above. When I insert a row, the first column's
conditional formatting isn't global to the column any more because the
references around which i've inserted have been linked to the old cells,
causing them to be unique. What's weird is the other two columns are
fine.
Here are the column conditional formatting rules, all have 3
Click to expand...
formulas
with
the first formatting the cell according to condition 2 and 3.

Column 1:
=AND(RC[-1]<>"", R[1]C<>RC)
=R[1]C<>RC
=RC<>""

Column 2:
=AND(R[1]C[-1]<>RC[-1],RC<>"")
=R[1]C[-1]<>RC[-1]
=(RC<>"")

Column 3:
=AND(RC[-1]<>"", R[1]C[-2]<>RC[-2])
= R[1]C[-2]<>RC[-2]
=RC[-1]<>""



It seems like the deeper I dig, the more holes I find.

Thanks,
Shawn

"Earl Kiosterud" <mvpearl omitthisword at verizon period net> wrote in
message Shawn,

Conditional formatting will apply formats for only one of the three
conditions. It has no choice if you have formats that are exclusive
(like
red for one condition, green for another, etc.). But I suspect you've
set
up formats that are not exclusive (like red for one condition,
Click to expand...
bold
for
another) where you'd like to have them both applied when both their
conditions are TRUE. But since it applies format(s) for only one
Click to expand...
of
the
three conditions, it stops after finding the first of the three formulas
that resolves to TRUE (thus prioritizing them for the case where more
than
one condition is satisfied -- it uses the first condition that is TRUE).
Remember that formats from any or all of the three tabs (font, border,
and
patterns) can be applied for each condition.

If you want to apply multiple formats, each with their own
Click to expand...
condition,
I
suspect you'll have to do that with a macro.

Or this the condition where I've TOTALLY missed the point?

Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

This is a bug in all versions of Excel.

When using conditional formatting with more than more formula, the
formatting ceases to go through all conditions once one condition
becomes
true.

This isn't the same behavior as when cell values are used. If
Click to expand...
this
is
the
intended behavior, there should at least be an option to continue
through
all conditions.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top