Generating work schdules based on the date entered

[ilovejellybeans]

Hey everyone,

My manager wants to create work schedules with excel. After browsing google and youtube, he typed this formula in cell C5 on the second tab
=INDEX('Option 1'!E3:'E44'!$C$4:$G$19,MATCH($A7,INDEX('[Schedule]Option 1'!$D$4:$G$19,,MATCH($A$2,'[Schedule]Option 1'!$D$4:$G$4)),0),1)

He wants schedules to appear based on the date that is typed in cell A2. When we try to change the date, a pop up appears saying update values: schedule. I select the name of the file and I choose the tab I want to update but it does not give the results I want. There is also a formula in L3 on the second tab but I am not sure why he needs that one. The formula is

=INDEX([Schedule]Schedule!$C$4:$G$19,MATCH($A7,INDEX([Schedule]Schedule!$D$4:$G$19,,MATCH($A$4,[Schedule]Schedule!$D$4:$G$4)),0),1)

For example if I typed 8/16 in cell A2, all the employees working that day for the first shift should be Matt, Alex, Joan, David, Valeria, Jimmy, Larry, Kim, Rachel, Olivia, Dwayne, Danny, Russ, Tyler, Frank, Linda, Tom, Hank, Liz and Antoine. We want all those names to appear in column C.

If anyone can provide the formula that can do what we need it to do or make the necessary changes so we can get what we need, that would be great.

[XLent]

If I've understood

Please Login or Register  to view this content.

as you modify A2 so the results will change.
for efficiency, you could / should move the MATCH re: $A$2 to a helper cell, so you calc once (as same for all rows), and then reference that helper cell instead