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:

Please Login or Register  to view this content.

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

in A12:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

in A12:

Please Login or Register  to view this content.

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

ok i'm gonna try to dissect this:

Please Login or Register  to view this content.

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")

Please Login or Register  to view this content.

I assume this part is where the names are?

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

Please Login or Register  to view this content.

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....