scheduling sheet sufferage

[byteblorg]

hey guys

i have this scheduling sheet which i need to automate.
specifically, in sheet1, my HR planner inputs the dates which each employee is working, over 5 days.

in sheet 2, i need to have formula driven range A2:J10
row1 (the date forecast), updates in tandem with the current date.

as the date changes, the sheet picks up who is planned for that day(ie on 11/07/15 adam is scheduled twice) from sheet1.

sheet1:

Person	Day1	Day2	Day3	Day4	Day5
adam	11/07/2015	12/07/2015	13/07/2015
shirley	13/07/2015	14/07/2015
john	09/11/2015	11/12/2015
adam	10/07/2015	11/07/2015	12/07/2015	13/07/2015	14/07/2015
tiffany	15/07/2015	16/07/2015	17/07/2015
shirley	11/12/2015	12/12/2015	13/12/2015
john	12/07/2015

<!--StartFragment--> <colgroup><col width="65" span="6" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

sheet2

10/07/2015	11/07/2015	12/07/2015	13/07/2015	14/07/2015	15/07/2015	16/07/2015	17/07/2015	18/07/2015	19/07/2015
	adam	adam	adam	shirley	tiffany	tiffany	tiffany
	adam	adam	shirley	tiffany
		john	adam

<colgroup><col span="10"></colgroup><tbody>
</tbody>

 

[mick0005]

Here you go. I think you made a few mistakes in your manual table on sheet 2. Adam should also exist once for 10/07/2015 and tiffany doesn't belong on 14/07/2015, but adam does.

I trust my formula more, so I think I have it right. You can drag the array formula down and across from A2

Excel 2010

	A	B	C	D	E	F
1	Person	Day1	Day2	Day3	Day4	Day5
2	adam	7/11/2015	7/12/2015	7/13/2015
3	shirley	7/13/2015	7/14/2015
4	john	11/9/2015	12/11/2015
5	adam	7/10/2015	7/11/2015	7/12/2015	7/13/2015	7/14/2015
6	tiffany	7/15/2015	7/16/2015	7/17/2015
7	shirley	12/11/2015	12/12/2015	12/13/2015
8	john	7/12/2015

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Excel 2010

	A	B	C	D	E	F	G	H	I	J
1	7/10/2015	7/11/2015	7/12/2015	7/13/2015	7/14/2015	7/15/2015	7/16/2015	7/17/2015	7/18/2015	7/19/2015
2	adam	adam	adam	adam	shirley	tiffany	tiffany	tiffany
3		adam	adam	shirley	adam
4			john	adam

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula A1 =TODAY() B1 =A1+1 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula A2 {=IFERROR(INDEX(Sheet1!$A$2:$A$8,SMALL(IF(Sheet1!$B$2:$F$8=A$1,ROW(Sheet1!$A$2:$A$8)-1),ROW(Sheet1!1:1))),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[byteblorg]

OH MY GOODNESS THAT'S AWESOME!!! it worked immediately! trillion thanks mick!

 

[mick0005]

Happy to help. Have a good weekend.