drop down list formula
 

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.

drop down list formula
 

Hi Eric,
take a look at Debra Web

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

"Eric" wrote:

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.

drop down list formula
 

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?



--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
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.

drop down list formula
 

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

"T. Valko" wrote:

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?



--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
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.

drop down list formula
 

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

"Eric" wrote:

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.

drop down list formula
 

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?


--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
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

"T. Valko" wrote:

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?



--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
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.

drop down list formula
 

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" wrote:

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?


--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
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

"T. Valko" wrote:

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?



--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
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.

drop down list formula
 

OK, that clears it up pretty much:

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

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
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" wrote:

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?


--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
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

"T. Valko" wrote:

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?



--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
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.

drop down list formula
 

That worked. Thank you :)

"T. Valko" wrote:

OK, that clears it up pretty much:

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

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
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" wrote:

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?


--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
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

"T. Valko" wrote:

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?



--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
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.

drop down list formula
 

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

"Paul C" wrote:

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

"Eric" wrote:

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.

drop down list formula
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
That worked. Thank you :)

"T. Valko" wrote:

OK, that clears it up pretty much:

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

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
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" wrote:

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?


--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
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

"T. Valko" wrote:

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?



--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
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.