Help with schedule

[0o0o0o0o]

I have a schedule for 50 people with names in a column A and days of the week
in first row: each employee can be assigned to one of four duty stations, be
off, or have vacation (that can be chosen from drop-down list) on a certain
day. That works fine, I get all numbers that I need, but I need separate
sheets for each duty station (4) with 7-10 employees. I tried to use Pivot
Table Report to have employee names show in each duty station sheet, but that
didn't work (if "John" worked at duty station 1 on Monday, but didn't on
Tuesday, his name still showed up on Tuesday's duty station sheet. Is there a
another way to extract names of the employees i.e. assigned to duty station 1
on Monday and so on? I also tried DGET function, but that didn't work either.
Any help would be greatly appreciated.

 

[Max]

One play to try out ..

In Sheet1
-------------
Assume the table below is in cols A to H,
headers in row1, data from row2 down

Names Mon Tue Wed Thu Fri Sat Sun
Name1 DS1 DS2 DS3 DS4 DS2 DS3 DS4
Name2 DS4 DS1 Vac DS3 DS4 Off DS1
Name3 DS2 DS3 DS2 DS1 DS3 DS1 DS2
Name4 DS3 Off DS4 DS2 DS4 DS4 DS3
Name5 DS1 DS4 Vac DS3 DS1 DS2 Off
etc

where:
DS1 = Duty Station1, DS2 = Duty Station2, etc
Vac = Vacation, Off = Day-off

Assuming empty cols to the right of col H

Put in J1:M1 : DS1, DS2, DS3, DS4

Put in I1: =Sheet2!A1

Put in J2:

=IF(INDIRECT(CHAR(MATCH($I$1,$A$1:$H$1,0)+64)&ROW())="","",IF(INDIRECT(CHAR(
MATCH($I$1,$A$1:$H$1,0)+64)&ROW())=J$1,ROW(),""))

Copy J2 across to M2, fill down by as many rows as
data is expected in cols A to H, say down to M100?
(should be more than enough, since the # of people is ~ 50)

In Sheet2
-------------
Let's reserve A1 for a data validation list
to select the "day-of-week"

Select A1
Click Data > Validation
Under Allow: choose List
Put in "Source:" box: Mon, Tue, Wed, Thu, Fri, Sat, Sun
Click OK

Put headers in A22 : DS1, DS2, DS3, DS4

Put in A3:

=IF(ISERROR(MATCH(SMALL(Sheet1!J:J,ROW(A1)),Sheet1!J:J,0)),"",OFFSET(Sheet1!
$A$1,MATCH(SMALL(Sheet1!J:J,ROW(A1)),Sheet1!J:J,0)-1,))

Copy A3 across to D3, fill down by as many rows
as was done in cols J to M in Sheet1

The above will give return nicely the schedule of names
by Duty Station # from Sheet1, by the day-of-week
selected in cell A1

If "Mon" is selected in A1, for the sample data,
you'll get:

Mon
DS1 DS2 DS3 DS4
Name1 Name3 Name4 Name2
Name5

Selecting "Fri" in A1 returns:

Fri
DS1 DS2 DS3 DS4
Name5 Name1 Name3 Name2
Name4

And so on ..

 

[Max]

Try this additional play as an extension
to the set-up suggested earlier ..

Assuming the same table in Sheet1, cols A to G

In Sheet1
-------------
Use empty cols O to V:

Put in P1:V1 : Mon, Tue, Wed, Thu, Fri, Sat, Sun

Put in O1: =Sheet3!A1

Put in P2: =IF(B2=$O$1,ROW(),"")
Copy P2 across to V2, fill down by as many rows as
data is expected in cols A to H, say down to V100

In a new Sheet3
---------------------
Like what was done in Sheet2
let's reserve A1 for a data validation list
to select the 4 Duty Stations (DS1 to DS4),
those on vacation (Vac) and those on day-off (Off)

Select A1
Click Data > Validation
Under Allow: choose List
Put in "Source:" box: DS1,DS2,DS3,DS4,Vac,Off
Click OK

Put headers in A2:G2 : Mon, Tue, Wed, Thu, Fri, Sat, Sun

Put in A3:

=IF(ISERROR(MATCH(SMALL(Sheet1!P,ROW(A1)),Sheet1!P,0)),"",OFFSET(Sheet1!
$A$1,MATCH(SMALL(Sheet1!P,ROW(A1)),Sheet1!P,0)-1,))

Copy A3 across to G3, fill down by as many rows
as was done in cols P to V in Sheet1,
viz. down to G100 thereabouts

The above will return the schedule of names
by day-of-week from Sheet1,
by the Duty Station # (DS#) selected in cell A1
(or by those on vacation, by those on day-off)

If "DS2" is selected in A1,
for the sample data, you'll get:

DS2
Mon Tue Wed Thu Fri Sat Sun
Name3 Name1 Name3 Name4 Name1 Name5 Name3

Selecting "DS4" in A1 returns:

DS4
Mon Tue Wed Thu Fri Sat Sun
Name2 Name5 Name4 Name1 Name2 Name4 Name1
_______________________Name4

Selecting "Vac" in A1 returns:

Vac
Mon Tue Wed Thu Fri Sat Sun
______Name2
______Name5

And so on ..

 

[0o0o0o0o]

Thank you very much, your help saved me a lot of time.

 

[Max]

You're welcome !
Thanks for the feedback ..