Conditional Formatting Row

[RyeDarrow]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I created a spreadsheet as a todo list. When the task becomes overdue, the entire row turns red. When a task is completed, it applies strike-through to the entire row. Last night the everything was working perfect.

However today, when a task becomes over due, only 3 of the six rows turn red. I thought I might have made a formula error, but when I click on the cells still in white, THEY TURN RED!! (same story with completed tasks and strikethrough format).

So now the conditional formatting is off and on all over the place, with no rhyme or reason. The only way to correct it is by click on a cell where it hasn't been applied correctly.

Thoughts?

 

[CyberTaz]

Do the cells involved contain formulas? If so check in Excel> Preferences -
Calculation to make sure 'Calculate Sheets' is set to 'Automatic'.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[John_McGhie_[MVP]]

I would say that workbook has corrupted. They do, if you edit them often
enough...

I would re-construct it by copying and paste>Special>Formulas into a new
Workbook.

If you don't paste the formatting, just the formulas, that should rebuild a
clean copy that will last another few years.

If you copy any of the formatting, you risk copying the problem.

Cheers

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
I created a spreadsheet as a todo list. When the task becomes overdue, the
entire row turns red. When a task is completed, it applies strike-through to
the entire row. Last night the everything was working perfect.

However today, when a task becomes over due, only 3 of the six rows turn red.
I thought I might have made a formula error, but when I click on the cells
still in white, THEY TURN RED!! (same story with completed tasks and
strikethrough format).

So now the conditional formatting is off and on all over the place, with no
rhyme or reason. The only way to correct it is by click on a cell where it
hasn't been applied correctly.

Thoughts?

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[Bob Greenblatt]

I would say that workbook has corrupted. They do, if you edit them often
enough...

I would re-construct it by copying and paste>Special>Formulas into a new
Workbook.

If you don't paste the formatting, just the formulas, that should rebuild a
clean copy that will last another few years.

If you copy any of the formatting, you risk copying the problem.

Cheers




--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

John, I hate to bust in on this but I have NEVER found workbook
corruption in over 20 years working with Excel if the workbook never had
VBA or originated on a PC. Corruption, in my experience, just doesn't
happen as a result of normal updating.

I'll bet a bunch that what is wrong is that the conditional formatting
formulas just do not have the correct mix of relative and absolute
references.

For a cell that is not formatting properly, can you please post the
exact text of the conditional formula when that cell is highlighted.
Also describe the formatting for each condition and the cell's actual
contents; both the formula in the cell and its result.

 

[RyeDarrow]

Thanks for the quick responses. Here is the weird thing...after I posted yesterday i restarted my computer and then it was working perfectly. Then again today it wasn't.

So I took a 3 minute screen shot of the problem, but I forgot to turn the audio on. If you watch however you can see the issue clearly. Because I wanted to explain the issue, I started another screen shot (without even closing the doc or restarting the computer) and then it works perfectly. I sound like an idiot trying to explain the problem because the second time it works just perfect.

Take a look at both videos and tell me what you think?

Movie 1
http://gallery.me.com/rdarrow#100009

Movie 2
http://gallery.me.com/rdarrow#100008

 

[RyeDarrow]

Here are the conditional formatting formulas:

Strikethrough =IF($F15="yes",TRUE,FALSE)
nothing =IF($E15="",TRUE,FALSE)
red fill =IF($E15

 

[RyeDarrow]

Sorry I just noticed that the last formula didn't post correctly:
red fill =IF($E15

 

[John_McGhie_[MVP]]

If the formula posted correctly this time, it is certainly in error.

If E15 = what??

The formula must return either True or False for Conditional formatting to
use it.

Cheers

Sorry I just noticed that the last formula didn't post correctly:
red fill =IF($E15

I have calculation set to automatic. The doc originated on a Mac, but i have
changed some of the standard colors?

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[RyeDarrow]

Sorry again...for some reason when I post that last formula it show in the box, but when I click submit, it cuts off the rest. I am going to type it instead of pasting, hopefully that will fix the issue.

=if($e15

 

[RyeDarrow]

see it cut it off again?....weird. Maybe it doesnt like the less than sign

= if($e15 is less than today(), true, false)

 

[John_McGhie_[MVP]]

Gotcha...

Sorry, my guess was "Corrupt workbook". Bob Greenblat doesn't believe it,
and he knows more than me, so we'll have to wait for him to get back...

Cheers

see it cut it off again?....weird. Maybe it doesnt like the less than sign

= if($e15 is less than today(), true, false)

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[Bob Greenblatt]

see it cut it off again?....weird. Maybe it doesnt like the less than sign

= if($e15 is less than today(), true, false)

Can you please explain what you are trying to do in English.

First, your conditional formulas are structured incorrectly. All you
need is an experssion that evaluates as true or false. For example, you
stated: Strikethrough =IF($F15="yes",TRUE,FALSE) What you really want to
say is: =$F15="yes"
that's all you need. It is true or it isn't.

It looks like what is happening, is you have conflicting conditions. If
column F is true you want one condition. If Column E is blank you want
another condition. What do you want if E is blank AND F is Yes? I think
this is where you are going wrong. Explain clearly what you expect to
have happen.

 

[RyeDarrow]

> Can you please explain what you are trying to do in English.
>
> First, your conditional formulas are structured incorrectly. All you
> need is an experssion that evaluates as true or false. For example, you
> stated: Strikethrough =IF($F15="yes",TRUE,FALSE) What you really want to
> say is: =$F15="yes"
> that's all you need. It is true or it isn't.
>
> It looks like what is happening, is you have conflicting conditions. If
> column F is true you want one condition. If Column E is blank you want
> another condition. What do you want if E is blank AND F is Yes? I think
> this is where you are going wrong. Explain clearly what you expect to
> have happen.
>
> --
>
> Bobgreenblattatmsndotcom
>

Bob, thanks for your help. I made the change on the strikethrough formula and it seems to be fixing the problem. However, that still doesnt explain the videos I submitted. Why was the other formula working sometimes, and then other times not? On back to back videos it went from not working to working.

From my experience, if a formula is wrong, nothing works. This seemed to be working 50% of the time?