Auto populate data for Roster

Based on my example table, i need a way to extract the data from the roster, so that i would be able to automatically populate the yellow areas of the 'AMPM' sheet with the names of the people working the respective shift.

An example of a desired outcome is also included

Thanks for the help in advance!

In A4 of AMPM sheet:

=IF(ROWS($A$1:$A1)>COUNTIF('Daily Roster'!B$2:B$5,"AM"),"",INDEX('Daily Roster'!$A$2:$A$5,SMALL(IF('Daily Roster'!B$2:B$5="AM",ROW('Daily Roster'!B$2:B$5)-ROW('Daily Roster'!B$2)+1),ROWS($A$1:$A1))))

confirmed with CTRL+SHIFT+ENTER not just ENTER, then copied down and across the top table.

in A12:

=IF(ROWS($A$1:$A1)>COUNTIF('Daily Roster'!B$2:B$5,"PM"),"",INDEX('Daily Roster'!$A$2:$A$5,SMALL(IF('Daily Roster'!B$2:B$5="PM",ROW('Daily Roster'!B$2:B$5)-ROW('Daily Roster'!B$2)+1),ROWS($A$1:$A1))))

confirmed with CTRL+SHIFT+ENTER not just ENTER, then copied down and across the bottom table.

Originally Posted by NBVC

In A4 of AMPM sheet:

=IF(ROWS($A$1:$A1)>COUNTIF('Daily Roster'!B$2:B$5,"AM"),"",INDEX('Daily Roster'!$A$2:$A$5,SMALL(IF('Daily Roster'!B$2:B$5="AM",ROW('Daily Roster'!B$2:B$5)-ROW('Daily Roster'!B$2)+1),ROWS($A$1:$A1))))

confirmed with CTRL+SHIFT+ENTER not just ENTER, then copied down and across the top table.

in A12:

=IF(ROWS($A$1:$A1)>COUNTIF('Daily Roster'!B$2:B$5,"PM"),"",INDEX('Daily Roster'!$A$2:$A$5,SMALL(IF('Daily Roster'!B$2:B$5="PM",ROW('Daily Roster'!B$2:B$5)-ROW('Daily Roster'!B$2)+1),ROWS($A$1:$A1))))

confirmed with CTRL+SHIFT+ENTER not just ENTER, then copied down and across the bottom table.

ok i'm gonna try to dissect this:

COUNTIF('Daily Roster'!B$2:B$X,"AM"),

this is the day (in this case, Monday) along with the shift type? So if i want wednesday it would be ('Daily Roster'!C$2:C$X, "AM")

"",INDEX('Daily Roster'!$A$2:$A$X,

I assume this part is where the names are?

also would X in the above 2 examples be the last populated row?

SMALL(IF('Daily Roster'!B$2:B$X="AM",ROW('Daily Roster'!B$2:B$5)-ROW('Daily Roster'!B$2)+1),ROWS($A$1:$A1))))

not sure what the rest of this means (looks like a way to check the next available row) but i assume the only thing i need to change is the X value right?

Yes, all you should need to do is replace the X with last row populated in the lookup table.

Then confirm the formula with CTRL+SHIFT+ENTER keys hit all together... then copy down and across.

Here is an explanation I gave a while back on this formula construct:

http://www.excelforum.com/excel-work...-question.html

Note though.. this is an array formula and can be resource intensive, if it is used in many cells of your spreadsheet, or if the lookup table is very large.

In those cases, there are alternatives, but they would involve use of helper columns to reduce the workload....