Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formatting cell based upon values w/in a selection
Background: I have a spreadsheet which tracks 10+ training course dates for
100+ individuals. All dates have conditional formatting applied to turn red if the date is <=TODAY(), yellow if btwn TODAY()+1 and TODAY()+30, and amber if the date in the cell is btwn TODAY()+31 and TODAY()+60. Dilemma: Column A, for each individual, needs to reflect their overall status (preferrably using the stoplight icon set) in their training. If any of their items are in red, Column A would show the red stoplight...if no items are red but yellow or amber dates are present, Column A would show the yellow stoplight...if none of any individual's dates are highlighted, a green stoplight would appear in Column A. I haven't found any items within the discussion group or through the Help function that are identical to what I'm looking for...but have made attempts to manipulate the formulas of what was offered with no results. Any help would be HIGHLY appreciated! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formatting cell based upon values w/in a selection
You'll need to apply a conditional format to the A column based on TODAY() as
well. Assume your other dates that you've conditionally formatted are in columns C:F Column A conditional format condition1 =MIN(C2:F2)<=TODAY() condition2 =AND(MIN(C2:F2)=TODAY()+1,MIN(C2:F2)<=TODAY()+30) conidtion3 AND(MIN(C2:F2)=TODAY()+31,MIN(C2:F2)<=TODAY()+60) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Carissa" wrote: Background: I have a spreadsheet which tracks 10+ training course dates for 100+ individuals. All dates have conditional formatting applied to turn red if the date is <=TODAY(), yellow if btwn TODAY()+1 and TODAY()+30, and amber if the date in the cell is btwn TODAY()+31 and TODAY()+60. Dilemma: Column A, for each individual, needs to reflect their overall status (preferrably using the stoplight icon set) in their training. If any of their items are in red, Column A would show the red stoplight...if no items are red but yellow or amber dates are present, Column A would show the yellow stoplight...if none of any individual's dates are highlighted, a green stoplight would appear in Column A. I haven't found any items within the discussion group or through the Help function that are identical to what I'm looking for...but have made attempts to manipulate the formulas of what was offered with no results. Any help would be HIGHLY appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating values based on selection from a drop-down list in Exc | Excel Worksheet Functions | |||
List box change values based on selection | Excel Discussion (Misc queries) | |||
Excel - Sum of Values based on adjacent list selection | New Users to Excel | |||
selection based on a date in a cell | Excel Discussion (Misc queries) | |||
limit cell list selection based on the selection of another list | Excel Worksheet Functions |