Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
reporting list of dates
Hi all,
i have a worksheet with 'col A' a list of dates and 'row 14' a list of names. under the names appears the text "late" to line up with a date. I have a sumproduct section to total the no of times the text "late" appears within a date range. On another worksheet i want to list the names in one column and then the dates where "late" appears in adjacent columns, but only if the sumproduct total is 9 I'm a bit lost at this point any help appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
reporting list of dates
Here's one formulas set-up which delivers close fit results that might appeal
to you Assume that in a source sheet named: x, you have 3 names listed in B14:D14 with real dates listed in A15:A300 and with "late" labels appearing within B15:D300 for latecomers In F15: =IF(B15="late",ROWS($1:1),"") Copy across to H15, fill down to H300 Then in another sheet, In A2: =IF(COUNT(OFFSET(x!$F$15:$F$300,,ROWS($1:1)-1))<=9,"",INDEX(x!$B$14:$D$14,,ROWS($1:1))) Copy A2 down to A4. Only names with more than 9 "late" labels in x will appear (this is your 9 times "late" limit). Just adapt the "<=9" bit in the formula to suit should you decide to change this control criteria. Then, to extract the full list of all the "late" dates horizontally for each name which appears within A2:A4, with dates neatly bunched to the left In B2: =IF($A2="","",IF(COLUMNS($A:A)COUNT(OFFSET(x!$F$1 5:$F$300,,ROWS($1:1)-1)),"",INDEX(x!$A$15:$A$300,SMALL(OFFSET(x!$F$15:$ F$300,,ROWS($1:1)-1),COLUMNS($A:A))))) Copy B2 across by as many cols as the max expected no. of "late" dates per name (this maximum may actually be further associated with your internal staff punctuality controls), say across by 15 cols to P2, fill down to P4. Success? Savour it, click the YES button below. -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "peterpeter" wrote: i have a worksheet with 'col A' a list of dates and 'row 14' a list of names. under the names appears the text "late" to line up with a date. I have a sumproduct section to total the no of times the text "late" appears within a date range. On another worksheet i want to list the names in one column and then the dates where "late" appears in adjacent columns, but only if the sumproduct total is 9 I'm a bit lost at this point any help appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I generate a list of random dates from dates I specify | Excel Worksheet Functions | |||
using excel to pick data from a list, reporting names and integers | Excel Discussion (Misc queries) | |||
How to filter out all the partial dates from a big list of dates? | Excel Discussion (Misc queries) | |||
Need to pull current dates from list w/many dates | Excel Discussion (Misc queries) | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions |