#VALUE! problem; not enough cond formatting slots to fix.

[StargateFanFromWork]

I usu. deal with #VALUE! with conditional formatting and "iserror". In a
current spreadsheet, I can't fix errors because I need alternating row
colours which take 2 of the 3 conditional formatting slots. To fix this for
the 2 colours of rows, I'd need an additional conditional formatting slot
(i.e., 4 instead of 3, total) so that the font colours could be accommodated
against 2 backgrounds.

.... the cell that gets a #VALUE! in it is a calcuation between dates. The
first date is typed into a cell to the left of this calculation one and it
corresponds to the date that that payment period ends (always a Friday) and
the calculation cell in question displays the date of the following Thursday
which is when the payment always goes through. Periodically but only once
in a blue moon, additional payments are recvd. I've found the easiest thing
to do is to just input the date in a format that is not the format
corresponding to the one set in the regional setting, a non-compliant date
format, as it were. This is not a problem as having a "properly" formatted
date isn't necessary here, only the info displayed to the user is. But that
results in the date calculation cell showing #VALUE! which I don't know how
fix since there aren't enough cond formatting places left. I have only been
able to blank out the font colour for one row colour.

Is there another way to make the #VALUE! have the same colour as the
background so it "disappears"?

 

[Niek Otten]

What is your formula?

Instead of putting the test for error in CF, you can also put it in the formula itself:

=IF(ISERROR(YourFormula),"",YourFormula)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I usu. deal with #VALUE! with conditional formatting and "iserror". In a
| current spreadsheet, I can't fix errors because I need alternating row
| colours which take 2 of the 3 conditional formatting slots. To fix this for
| the 2 colours of rows, I'd need an additional conditional formatting slot
| (i.e., 4 instead of 3, total) so that the font colours could be accommodated
| against 2 backgrounds.
|
| ... the cell that gets a #VALUE! in it is a calcuation between dates. The
| first date is typed into a cell to the left of this calculation one and it
| corresponds to the date that that payment period ends (always a Friday) and
| the calculation cell in question displays the date of the following Thursday
| which is when the payment always goes through. Periodically but only once
| in a blue moon, additional payments are recvd. I've found the easiest thing
| to do is to just input the date in a format that is not the format
| corresponding to the one set in the regional setting, a non-compliant date
| format, as it were. This is not a problem as having a "properly" formatted
| date isn't necessary here, only the info displayed to the user is. But that
| results in the date calculation cell showing #VALUE! which I don't know how
| fix since there aren't enough cond formatting places left. I have only been
| able to blank out the font colour for one row colour.
|
| Is there another way to make the #VALUE! have the same colour as the
| background so it "disappears"?
|
|

 

[StargateFanFromWork]

What is your formula?

That is too kewl for words! Learn something new all the time.

Instead of putting the test for error in CF, you can also put it in the
formula itself:

=IF(ISERROR(YourFormula),"",YourFormula)

After some doing and playing with parentheses, what worked is this:
"=IF(ISERROR(IF($A54<>"",($A54+6),"")),"(n/a)",(IF($A54<>"",($A54+6),"")))"

I ended up putting "(n/a)" in the middle so that I get that as a result
under the pay date instead of it being blank and it's perfect.

I didn't think to put in my formula, which was silly. I should know better.
But got it to work just great. Thanks. D

 

[Gord Dibben]

BTW

You only need one CF condition to color alternate rows.

=MOD(ROW(),2)=1

Select a gaggle of rows and color them green or whatever.

Then with that gaggle still selected........CF>Formula is: enter the above and
format to a different color.


Gord Dibben MS Excel MVP

 

[StargateFanFromWork]

Reminds me of Spock's telling Kirk "... displaying his 2-dimensional
thinking ..." [about Kahn, a superior being (according to Kahn, of course)].
Well, I'm certainly no superior being, but I sure displayed my
2-dimensional, or less-than-intelligent, thinking here! <lol>

I'm absolutely _not_ going back to fix all the spreadsheets I've ever done
using 2 CF conditions, but I sure will keep this trick in mind and will
start using one condition from now on in future!! <bg>