Roster help

Welshjim22

New Member
Joined
Nov 20, 2018
Messages
2
I am creating a roster for work with some colleagues and trying to find a simple way to run a report. The roster already shows how many people on each shift via a counter. It is a large sheet dates from B9-B374 and names for staff T6 - DY6. What I need is a simple way where you could enter the sunday date, B9- B15 for example, and it would look at each day for a week and see who was on an Early and where an early is recorded it would look at the name for that person which would be in columns starting cell T6-DY6, and produce a list. Does anyone have any ideas please.

Thanks in advance
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
01/11/201802/11/201803/11/201804/11/201805/11/201806/11/201807/11/201808/11/201809/11/201810/11/201811/11/201812/11/201813/11/201814/11/2018
alanLLLEEEEELL
billEEEEELLLLLL
colinLLEEEEEL
daveLLLLLEEEE
edLLLEEEEELL
fredEEEEELLLLLL
georgeLLEEEEEL
harryLLLLLEEEE
ianLLLEEEEELL
jamesEEEEELLLLLL
ENTER A DATE01/11/2018
7 DAYS COMMENCING 1/11/18
helper
01/11/201802/11/201803/11/201804/11/201805/11/201806/11/201807/11/2018
alan11alan2
bill11111bill5
colin1colin1
dave
ed11ed2
fred11111fred5
george1george1
harry
ian11ian2
james11111james5
the table above populates with a 1 if the corresponding cell in the top table = "E"
then if the row total is greater than 0 it puts the name
then if there is a name it sums the 1's

<colgroup><col><col span="2"><col span="3"><col><col span="8"><col span="2"></colgroup><tbody>
</tbody>
 
Upvote 0
01/11/201802/11/201803/11/201804/11/201805/11/201806/11/201807/11/201808/11/201809/11/201810/11/201811/11/201812/11/201813/11/201814/11/2018
alanLLLEEEEELL
billEEEEELLLLLL
colinLLEEEEEL
daveLLLLLEEEE
edLLLEEEEELL
fredEEEEELLLLLL
georgeLLEEEEEL
harryLLLLLEEEE
ianLLLEEEEELL
jamesEEEEELLLLLL
ENTER A DATE03/11/2018< < < <type in a date here
these date headers generated from the date entered above
helper
03/11/201804/11/201805/11/201806/11/201807/11/201808/11/201809/11/2018
alan1111alan4
bill1111bill4
colin111colin3
dave
ed1111ed4
fred1111fred4
george111george3
harry
ian1111ian4
james1111james4
the table above populates with a 1 if the corresponding cell in the top table = "E"
then if the row total is greater than 0 it puts the name
then if there is a name it sums the 1's
formula returning a blank for alan on 3/11/18 is
=IF(OFFSET($A$1,ROW()-25,MATCH(B$25,$B$1:$O$1,0))="E",1,"")

<colgroup><col><col span="2"><col span="3"><col><col span="8"><col></colgroup><tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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