Join 3,494 readers in helping fund MetaFilter (Hide)


Conditional formatting, with limitations.
June 2, 2012 9:12 AM   Subscribe

How do I set conditional formatting in Excel based on whether a list value has been selected in a range of other cells?

I need to be able to format a header cell (B1, for example) based on whether or not other cells in that row have been filled in. The other cells will have a yes or no value in a drop-down menu, using the list function under Data, Validity. Snowflake issue: I'm using OpenOffice.org - calc. Ugh. Any ideas?
posted by doyouknowwhoIam? to Computers & Internet (1 answer total)
 
Not sure about OpenOffice Calc, but in Excel I'd set it up like this:

B1 - Header - that you wanted colour if all/some (?) cells below are filled in
B2 - Formula that counts how many of the below cells are a yes/no (depending on what you're looking for)

B3 - Bwhatever - your cells with the yes/no value

You can then conditionally format B1 based on the contents of B2.


Counting/summing cells based on fill/font colour requires a VBA formulae like this one.

It's doable, but if the colour is based on the contents of the cell, then count based on what triggers the colour, rather than by the colour.

This is the way I'd set it up, I always try to avoid having data stored as a colour, as it is so easy to accidentally clear. For example, if I want to mark a task as done with a green background, rather than doing it manually using the fill, I'll add another column titled done, then put a Yes in each one I've done, and colour the row using conditional formatting based on that column.
posted by chrispy108 at 11:37 AM on June 2, 2012


« Older I'm a woman who identifies as ...   |  How dangerous is this part of ... Newer »
This thread is closed to new comments.