Here's one set-up to try ..
Assume there are 11 shifts, numbered: 1,2,3 ... 11
Let's take the sample table below, assumed in Sheet1, A1:H5
----Sun Mon Tue Wed Thu Fri Sat
Joe 6 1 9 8 1 2 7
Bill 7 2 9 5 8 6 8
Bob 7 4 8 1 9 8 8
Frank 11 10 6 5 10 2 9
Name the range B1:H1 (i.e.: Sun, Mon, Tues ... Sat) as : WDay
(this named range will be used in a DV cell we're creating in Sheet2's A1
later)
Put in J1: =Sheet2!A1
List across in K1:U1, the 11 shift #s: 1, 2, 3 ... 11
Put in K2:
=IF(ISNA(MATCH(K$1,OFFSET($A2,,MATCH($J$1,$B$1:$H$ 1,0)),0)),"",ROW())
Copy K2 across to U2, fill down to say U100
to cover the max expected number of staff in col A
(can copy down ahead of expected data input)
In Sheet2
---------
Let's create a data validation list in A1 to select the day
Select A1, click Data Validation
Make the settings as:
Allow: List, Source: =WDay
Click OK
List across in A2:K2, the 11 shift #s: 1, 2, 3 ... 11
Put in A3:
=IF(ISERROR(SMALL(Sheet1!K$2:K$100,ROWS($A$1:A1))) ,"",IF(INDEX(Sheet1!$A$2:$
A$100,MATCH(SMALL(Sheet1!K$2:K$100,ROWS($A$1:A1)), Sheet1!K$2:K$100,0))=0,"",
INDEX(Sheet1!$A$2:$A$100,MATCH(SMALL(Sheet1!K$2:K$ 100,ROWS($A$1:A1)),Sheet1!
K$2:K$100,0))))
Copy A3 across to K3, fill down to K101
(cover the same range as in Sheet1)
Test it out. Select in A1: Sun (say).
You'll get:
6 7 11
Joe Bill Frank
Bob
Try select in A1: Tues, and you'll get
6 8 9
Frank Bob Joe
Bill (below Joe)
And so on ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"BW" wrote in message
...
I am trying to manipulate a fairly simple weekly schedule.
On Sheet 1, column A contains names, Row 1 contains Days of the week. The
cells in column B contain a number indicating the shift that the person
is
supposed to work. It looks like this:
Sun
Joe 6
Bill 7
Bob 7
Frank 11
On Sheet 2, I want to turn this data around so that the column headings
are
the shift time and underneath there is a list of people on each shift. It
should look like:
6 7 11
Joe Bill Frank
Bob
Unfortunately, there may be a different number of people on each shift
each
day, so a simple sort isn't enough. Note that each day will have it's own
sheet (2-8) drawing its' data from the proper column.
How do I do it?