FILL FUNCTION NEEDED

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
hansonb4 said:
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.
Click to expand...

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)

---
 
Thanks, Max.

Max said:
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)

---
Click to expand...
 
Back
Top