Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
FILL FUNCTION NEEDED
I am tracking 150 open issues, each one having the date the issue opened in
column E. I need to know when one of the open items reaches 45 days aging by having that row moved to row 1 and the fill of each cell in that row highlighted yellow. Any rows that have a date in column E that reaches an aging of 90 days needs to have priority, inserted above the yellow rows and be filled red. Please help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
FILL FUNCTION NEEDED
"hansonb4" wrote:
I am tracking 150 open issues, each one having the date the issue opened in column E. I need to know when one of the open items reaches 45 days aging by having that row moved to row 1 and the fill of each cell in that row highlighted yellow. Any rows that have a date in column E that reaches an aging of 90 days needs to have priority, inserted above the yellow rows and be filled red. Here's a way using non-array formulas to achieve the auto-tracking in a new sheet .. Assume source data is in a sheet: X, within cols A to E, from row1 down. Col E = dates (the key col) In a new sheet: Y (say), In A1: =IF(X!E1="","",X!E1+ROW()/10^10) In B1: =IF(ROW()COUNT($A:$A),"",INDEX(X!A:A,MATCH(SMALL( $A:$A,ROW()),$A:$A,0))) Copy B1 to F1 Select A1:F1, copy down to cover the max expected extent of source data in X. Format col F as dates. Cols B to F will return an auto-ascending sort of lines (by the date col E) from X. All lines will be neatly bunched at the top, with earliest dates on top. Lines with tied dates (if any) will appear in the same relative order that they are within X. Then just apply Conditional Formatting (CF) to sheet: Y as follows: Select the entire sheet Y (with A1 active), apply the CF using: Condition1: =AND($F1<"",TODAY()-$F1=90) Format red fill Condition2 =AND($F1<"",TODAY()-$F1=45,TODAY()-$F1<90) Format yellow fill The above will color lines aged 90 days or more with red fill, while lines aged between 45 and 90 days will appear yellow filled (Hide away col A if desired) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
FILL FUNCTION NEEDED
Thanks, Max.
"Max" wrote: "hansonb4" wrote: I am tracking 150 open issues, each one having the date the issue opened in column E. I need to know when one of the open items reaches 45 days aging by having that row moved to row 1 and the fill of each cell in that row highlighted yellow. Any rows that have a date in column E that reaches an aging of 90 days needs to have priority, inserted above the yellow rows and be filled red. Here's a way using non-array formulas to achieve the auto-tracking in a new sheet .. Assume source data is in a sheet: X, within cols A to E, from row1 down. Col E = dates (the key col) In a new sheet: Y (say), In A1: =IF(X!E1="","",X!E1+ROW()/10^10) In B1: =IF(ROW()COUNT($A:$A),"",INDEX(X!A:A,MATCH(SMALL( $A:$A,ROW()),$A:$A,0))) Copy B1 to F1 Select A1:F1, copy down to cover the max expected extent of source data in X. Format col F as dates. Cols B to F will return an auto-ascending sort of lines (by the date col E) from X. All lines will be neatly bunched at the top, with earliest dates on top. Lines with tied dates (if any) will appear in the same relative order that they are within X. Then just apply Conditional Formatting (CF) to sheet: Y as follows: Select the entire sheet Y (with A1 active), apply the CF using: Condition1: =AND($F1<"",TODAY()-$F1=90) Format red fill Condition2 =AND($F1<"",TODAY()-$F1=45,TODAY()-$F1<90) Format yellow fill The above will color lines aged 90 days or more with red fill, while lines aged between 45 and 90 days will appear yellow filled (Hide away col A if desired) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
FILL FUNCTION NEEDED
You're welcome!
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "hansonb4" wrote: Thanks, Max. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP NEEDED FOR IF Function Inside a Macro | Excel Worksheet Functions | |||
numerical integration | Excel Discussion (Misc queries) | |||
how do I change the fill collor os a cell using a function in VB | Excel Discussion (Misc queries) | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
clock | Excel Worksheet Functions |