Index Match Help
Here's one way...
On Sheet2 you'll have to include dates as a column header and you'll have to
repeat the date like this:
|8/16/2009......|8/16/2009....|8/17/2009.......|8/17/2009......|
| Sunday Start | Sunday End | Monday Start | Monday End |
Let's assume your data on Sheet1 is in the range A2:D9
Sheet2 B1:O1 = dates as shown above
Sheet2 A2 = some ID number
Enter this formula in B2:
=SUMPRODUCT(--(Sheet1!$A$2:$A$9=$A2),--(Sheet1!$B$2:$B$9=B$1),INDEX(Sheet1!$C$2:$D$9,,COU NTIF($B$1:B$1,B$1)))
Copy across to O2
Custom format B2:O2 as h:mm;;;
That will suppress the results that end up as 0.
--
Biff
Microsoft Excel MVP
"Sara" wrote in message
...
Hi there
I'm trying to create a index/match formula for rosters.
Sheet 1 looks like this
ID | Shift Day | Start | End
113809 | 17-Aug | 13:00 | 17:00
113809 | 18-Aug | 13:00 | 17:00
113809 | 19-Aug | 13:00 | 17:00
113809 | 20-Aug | 13:00 | 17:00
113809 | 21-Aug | 13:00 | 17:00
148044 | 17-Aug | 13:00 | 20:35
148044 | 18-Aug | 13:00 | 20:35
148044 | 19-Aug | 13:00 | 20:35
And I'm trying to change it into the following format on sheet 2
ID | Sunday Start | Sunday End | Monday Start | Monday End | Tuesday Start
.etc
No one works all 7 days of the week so I know I need some sort of ISERROR
or
ISNA as well
The frustrating thing is that I did this last year and can't find the
answer
|