scheduling sheet sufferage

B

byteblorg

New Member
Joined
Jun 28, 2015
Messages
5
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:

[TABLE="width: 390"]
<!--StartFragment--> <colgroup><col width="65" span="6" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 65"]Person[/TD]
[TD="width: 65"]Day1[/TD]
[TD="width: 65"]Day2[/TD]
[TD="width: 65"]Day3[/TD]
[TD="width: 65"]Day4[/TD]
[TD="width: 65"]Day5[/TD]
[/TR]
[TR]
[TD]adam[/TD]
[TD="class: xl63, align: right"]11/07/2015[/TD]
[TD="class: xl63, align: right"]12/07/2015[/TD]
[TD="class: xl63, align: right"]13/07/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]shirley[/TD]
[TD="class: xl63, align: right"]13/07/2015[/TD]
[TD="class: xl63, align: right"]14/07/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD="class: xl63, align: right"]09/11/2015[/TD]
[TD="class: xl63, align: right"]11/12/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]adam[/TD]
[TD="class: xl63, align: right"]10/07/2015[/TD]
[TD="class: xl63, align: right"]11/07/2015[/TD]
[TD="class: xl63, align: right"]12/07/2015[/TD]
[TD="class: xl63, align: right"]13/07/2015[/TD]
[TD="class: xl63, align: right"]14/07/2015[/TD]
[/TR]
[TR]
[TD]tiffany[/TD]
[TD="class: xl63, align: right"]15/07/2015[/TD]
[TD="class: xl63, align: right"]16/07/2015[/TD]
[TD="class: xl63, align: right"]17/07/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]shirley[/TD]
[TD="class: xl63, align: right"]11/12/2015[/TD]
[TD="class: xl63, align: right"]12/12/2015[/TD]
[TD="class: xl63, align: right"]13/12/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD="class: xl63, align: right"]12/07/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

sheet2

[TABLE="width: 670"]
<colgroup><col span="10"></colgroup><tbody>[TR]
[TD="align: right"]10/07/2015[/TD]
[TD="align: right"]11/07/2015[/TD]
[TD="align: right"]12/07/2015[/TD]
[TD="align: right"]13/07/2015[/TD]
[TD="align: right"]14/07/2015[/TD]
[TD="align: right"]15/07/2015[/TD]
[TD="align: right"]16/07/2015[/TD]
[TD="align: right"]17/07/2015[/TD]
[TD="align: right"]18/07/2015[/TD]
[TD="align: right"]19/07/2015[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]adam[/TD]
[TD]adam[/TD]
[TD]adam[/TD]
[TD]shirley[/TD]
[TD]tiffany[/TD]
[TD]tiffany[/TD]
[TD]tiffany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]adam[/TD]
[TD]adam[/TD]
[TD]shirley[/TD]
[TD]tiffany[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]john[/TD]
[TD]adam[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
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
ABCDEF
PersonDay1Day2Day3Day4Day5
adam
shirley
john
adam
tiffany
shirley
john

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]7/11/2015[/TD]
[TD="align: right"]7/12/2015[/TD]
[TD="align: right"]7/13/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]7/13/2015[/TD]
[TD="align: right"]7/14/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]11/9/2015[/TD]
[TD="align: right"]12/11/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]7/10/2015[/TD]
[TD="align: right"]7/11/2015[/TD]
[TD="align: right"]7/12/2015[/TD]
[TD="align: right"]7/13/2015[/TD]
[TD="align: right"]7/14/2015[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]7/15/2015[/TD]
[TD="align: right"]7/16/2015[/TD]
[TD="align: right"]7/17/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]12/11/2015[/TD]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]7/12/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​




Excel 2010
ABCDEFGHIJ

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]7/10/2015[/TD]
[TD="align: center"]7/11/2015[/TD]
[TD="align: center"]7/12/2015[/TD]
[TD="align: center"]7/13/2015[/TD]
[TD="align: center"]7/14/2015[/TD]
[TD="align: center"]7/15/2015[/TD]
[TD="align: center"]7/16/2015[/TD]
[TD="align: center"]7/17/2015[/TD]
[TD="align: center"]7/18/2015[/TD]
[TD="align: center"]7/19/2015[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]adam[/TD]
[TD="align: center"]adam[/TD]
[TD="align: center"]adam[/TD]
[TD="align: center"]adam[/TD]
[TD="align: center"]shirley[/TD]
[TD="align: center"]tiffany[/TD]
[TD="align: center"]tiffany[/TD]
[TD="align: center"]tiffany[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]adam[/TD]
[TD="align: center"]adam[/TD]
[TD="align: center"]shirley[/TD]
[TD="align: center"]adam[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]john[/TD]
[TD="align: center"]adam[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A1[/TH]
[TD="align: left"]=TODAY()[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=A1+1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Median with multiple conditions and date
Replies
3
Views
61
Peter_SSs
Peter_SSs
L
  • Solved
Help Please
2 3
Replies
21
Views
702
Asbestos_Jen
Asbestos_Jen
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
326
Raj1979
R
A
  • Solved
Count entry by time
Replies
3
Views
344
admvlad
A
E
  • Solved
Ranking Invoices By Highest Amount By "Year to Date"
Replies
2
Views
211
ellison
E

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top