| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula and Drop Down List | Excel Discussion (Misc queries) | |||
| VLookup: Can formula use value loaded from a drop down list? | Excel Discussion (Misc queries) | |||
| formula on link and drop down list details | Excel Discussion (Misc queries) | |||
| formula drop down list | New Users to Excel | |||
| multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) | |||
Linear Mode
