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

"Max" wrote:

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:P,ROW(A1)),Sheet1 !P:P,0)),"",OFFSET(Sheet1!
$A$1,MATCH(SMALL(Sheet1!P:P,ROW(A1)),Sheet1!P: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 ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----