drop down list formula

[Eric]

Basically what I'm trying to do is I have cells G18 through G32 that have
drop down values of "Green", "Yellow", and "Red". I have another cell (let's
call it C7) that I want to display one of these colors as a summary cell. For
example if all the G cells are "Green" then C7 is "Green", but if any of the
G cells is either "Yellow" or "Red" then C7 will display as either "Yellow"
or "Red" ("Red" if both are present). Any help with this formula would be
welcome. Thanks.

 

[Eduardo]

Hi Eric,
take a look at Debra Web

http://www.contextures.com/xlDataVal02.html#TwoWord

 

[T. Valko]

It's not real clear what you want...

Is this what you want:

If *all* cells are the same color then that color

If *any* cell is Red then Red

What if some cells are Green and some cells are Yellow?

Will all cells contain some color? Any empty cells?

 

[Eric]

If all cells are green then green.
If any cells yellow or red then yellow or red.
If there is a mix of all three then red (or if just yellow and green then
yellow).
Green is the lowest priority then yellow then red.
There are no empty cells.
I hope this clarifies what I need.

Thank you

 

[Paul C]

Eric,

I have a worksheet that does exactly this.

If you put a conditional format on C7 with 3 conditions this will work.

Condition 1
Formula Is =COUNTIF($G$18:$G$32,"Red")>0
Select the Red Fill as the format

Condition 2
Formula Is =COUNTIF($G$18:$G$32,"Yellow")>0
Select the Yellow Fill as the format

Condition 3
Formula Is =COUNTIF($G$18:$G$32,"Green")>0
Select the Green Fill as the format

Paul C

 

[T. Valko]

I hope this clarifies what I need.

Well, this is still confusing:

If any cells yellow or red then yellow or red.

So, should that be Yellow or Red? If all cells are either Yellow or Red what
determines if the result is Yellow or Red?

 

[Eric]

It's a progressive kind of result. If all are green then the result is green
(but all must be green). If even a single cell is yellow then result is
yellow (if there is no red). If even a single cell is red then result is red
regardless of any other cell.

 

[T. Valko]

OK, that clears it up pretty much:

=IF(COUNTIF(G18:G32,"Red"),"Red",IF(COUNTIF(G18:G32,"Yellow"),"Yellow","Green"))

 

[Eric]

That worked. Thank you

OK, that clears it up pretty much:

=IF(COUNTIF(G18:G32,"Red"),"Red",IF(COUNTIF(G18:G32,"Yellow"),"Yellow","Green"))

 

[Eric]

These were very helpful as well. Couldn't find the conditionals in '07 for a
minute but tracked it down. Thank you.

 

[T. Valko]

You're welcome. Thanks for the feedback!