I have a long table which goes up to 22 weeks of days. Each cell in the
table refers to a shiuft (e.g. E1 early 1, M2 mid 2 etc). The numbers at the
top of the column are actually staff (1 might be Bill, 2 might be Ted etc)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
1 Mon E1 O O O L1 L2 L3 O O L4 L5 L6 O O M1 M2 E2 O O O E3 E4
2 Tue E2 E1 O O O L1 L2 L3 O O L4 L5 L6 O O M1 M2 E1 O O O E2
3 Wed E3 E2 E1 O O O L1 L2 L3 O O L4 L5 L6 O O M1 M2 E1 O O O


What I'm trying to achieve is filling out a second table which shows who is
doing each shift on a particular day e.g.:

E1 E2 E3 E4 M M L1 L2 L3 L4 L5 L6
MON 1 17 21 22 15 16 5 6 7 10 11 12
TUE
WED


So I'm trying to find a function that says 'return the text at the top of
the column in whichever column contains "E1" in row 1Mon" and then again for
"E2" etc and onwards for every day in 22 weeks.

It's a bit too complex for me so any help would be greatly appreciated.

Sorry - the formatting got lost. Hope it's still relativeley clear.

"merlin" wrote in message
...

I have a long table which goes up to 22 weeks of days. Each cell in the
table refers to a shiuft (e.g. E1 early 1, M2 mid 2 etc). The numbers at
the top of the column are actually staff (1 might be Bill, 2 might be Ted
etc)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
1 Mon E1 O O O L1 L2 L3 O O L4 L5 L6 O O M1 M2 E2 O O O E3 E4
2 Tue E2 E1 O O O L1 L2 L3 O O L4 L5 L6 O O M1 M2 E1 O O O E2
3 Wed E3 E2 E1 O O O L1 L2 L3 O O L4 L5 L6 O O M1 M2 E1 O O O


What I'm trying to achieve is filling out a second table which shows who
is doing each shift on a particular day e.g.:

E1 E2 E3 E4 M M L1 L2 L3 L4 L5 L6
MON 1 17 21 22 15 16 5 6 7 10 11 12
TUE
WED


So I'm trying to find a function that says 'return the text at the top of
the column in whichever column contains "E1" in row 1Mon" and then again
for "E2" etc and onwards for every day in 22 weeks.

It's a bit too complex for me so any help would be greatly appreciated.

The simplest way would be to setup those heading on Sheet2, and then add
this formula to B2

=SUMPRODUCT((Sheet1!$A$2:$A$4=$A2)*(Sheet1!$A$2:$W $4=B$1)*(Sheet1!$A$1:$W$1)
)

and copy down and across

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"merlin" wrote in message
...

I have a long table which goes up to 22 weeks of days. Each cell in the
table refers to a shiuft (e.g. E1 early 1, M2 mid 2 etc). The numbers at
the
top of the column are actually staff (1 might be Bill, 2 might be Ted etc)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
1 Mon E1 O O O L1 L2 L3 O O L4 L5 L6 O O M1 M2 E2 O O O E3 E4
2 Tue E2 E1 O O O L1 L2 L3 O O L4 L5 L6 O O M1 M2 E1 O O O E2
3 Wed E3 E2 E1 O O O L1 L2 L3 O O L4 L5 L6 O O M1 M2 E1 O O O


What I'm trying to achieve is filling out a second table which shows who
is
doing each shift on a particular day e.g.:

E1 E2 E3 E4 M M L1 L2 L3 L4 L5 L6
MON 1 17 21 22 15 16 5 6 7 10 11 12
TUE
WED


So I'm trying to find a function that says 'return the text at the top of
the column in whichever column contains "E1" in row 1Mon" and then again
for
"E2" etc and onwards for every day in 22 weeks.

It's a bit too complex for me so any help would be greatly appreciated.

Assume the first table is in a sheet named A and the new table is in a sheet
named B

Assume on the new sheet (B) Mon is in A2 and on the old sheet (A) Mon is in A1

in B2 of Sheet B

=INDEX(A!$B$1:$W1,1,MATCH(B$1,A!$B2:$W2,0))

then drag fill down and across

--
Regards,
Tom Ogilvy


"merlin" wrote:


I have a long table which goes up to 22 weeks of days. Each cell in the
table refers to a shiuft (e.g. E1 early 1, M2 mid 2 etc). The numbers at the
top of the column are actually staff (1 might be Bill, 2 might be Ted etc)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
1 Mon E1 O O O L1 L2 L3 O O L4 L5 L6 O O M1 M2 E2 O O O E3 E4
2 Tue E2 E1 O O O L1 L2 L3 O O L4 L5 L6 O O M1 M2 E1 O O O E2
3 Wed E3 E2 E1 O O O L1 L2 L3 O O L4 L5 L6 O O M1 M2 E1 O O O


What I'm trying to achieve is filling out a second table which shows who is
doing each shift on a particular day e.g.:

E1 E2 E3 E4 M M L1 L2 L3 L4 L5 L6
MON 1 17 21 22 15 16 5 6 7 10 11 12
TUE
WED


So I'm trying to find a function that says 'return the text at the top of
the column in whichever column contains "E1" in row 1Mon" and then again for
"E2" etc and onwards for every day in 22 weeks.

It's a bit too complex for me so any help would be greatly appreciated.

I'm not quite catching on to this. I think I've converted it from your
example but it doesn't work.

could you breakdown what each of those references are?


"Tom Ogilvy" wrote in message
...
Assume the first table is in a sheet named A and the new table is in a
sheet
named B

Assume on the new sheet (B) Mon is in A2 and on the old sheet (A) Mon is
in A1

in B2 of Sheet B

=INDEX(A!$B$1:$W1,1,MATCH(B$1,A!$B2:$W2,0))

then drag fill down and across

--
Regards,
Tom Ogilvy


"merlin" wrote:


I have a long table which goes up to 22 weeks of days. Each cell in the
table refers to a shiuft (e.g. E1 early 1, M2 mid 2 etc). The numbers at
the
top of the column are actually staff (1 might be Bill, 2 might be Ted
etc)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
1 Mon E1 O O O L1 L2 L3 O O L4 L5 L6 O O M1 M2 E2 O O O E3 E4
2 Tue E2 E1 O O O L1 L2 L3 O O L4 L5 L6 O O M1 M2 E1 O O O E2
3 Wed E3 E2 E1 O O O L1 L2 L3 O O L4 L5 L6 O O M1 M2 E1 O O O


What I'm trying to achieve is filling out a second table which shows who
is
doing each shift on a particular day e.g.:

E1 E2 E3 E4 M M L1 L2 L3 L4 L5 L6
MON 1 17 21 22 15 16 5 6 7 10 11 12
TUE
WED


So I'm trying to find a function that says 'return the text at the top of
the column in whichever column contains "E1" in row 1Mon" and then again
for
"E2" etc and onwards for every day in 22 weeks.

It's a bit too complex for me so any help would be greatly appreciated.

I did.

If you can't figure it out, send me a workbook with your two tables laid out
(first table populated) and I will show you.



--
Regards,
Tom Ogilvy



"merlin" wrote:

I'm not quite catching on to this. I think I've converted it from your
example but it doesn't work.

could you breakdown what each of those references are?


"Tom Ogilvy" wrote in message
...
Assume the first table is in a sheet named A and the new table is in a
sheet
named B

Assume on the new sheet (B) Mon is in A2 and on the old sheet (A) Mon is
in A1

in B2 of Sheet B

=INDEX(A!$B$1:$W1,1,MATCH(B$1,A!$B2:$W2,0))

then drag fill down and across

--
Regards,
Tom Ogilvy


"merlin" wrote:


I have a long table which goes up to 22 weeks of days. Each cell in the
table refers to a shiuft (e.g. E1 early 1, M2 mid 2 etc). The numbers at
the
top of the column are actually staff (1 might be Bill, 2 might be Ted
etc)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
1 Mon E1 O O O L1 L2 L3 O O L4 L5 L6 O O M1 M2 E2 O O O E3 E4
2 Tue E2 E1 O O O L1 L2 L3 O O L4 L5 L6 O O M1 M2 E1 O O O E2
3 Wed E3 E2 E1 O O O L1 L2 L3 O O L4 L5 L6 O O M1 M2 E1 O O O


What I'm trying to achieve is filling out a second table which shows who
is
doing each shift on a particular day e.g.:

E1 E2 E3 E4 M M L1 L2 L3 L4 L5 L6
MON 1 17 21 22 15 16 5 6 7 10 11 12
TUE
WED


So I'm trying to find a function that says 'return the text at the top of
the column in whichever column contains "E1" in row 1Mon" and then again
for
"E2" etc and onwards for every day in 22 weeks.

It's a bit too complex for me so any help would be greatly appreciated.

OK - got it working. This is the formula I've got:

=INDEX(A!$D$1:$Y$1,1,MATCH(C$1,A!$D2:Y2,0))

I think you had a $ missing in your '$W1' and should have been '$W$1' ?

Many thanks, Tom. I needed to be shown how to use the INDEX and MATCH
functions together.

regards
jc

"Tom Ogilvy" wrote in message
...
I did.

If you can't figure it out, send me a workbook with your two tables laid
out
(first table populated) and I will show you.



--
Regards,
Tom Ogilvy



"merlin" wrote:

I'm not quite catching on to this. I think I've converted it from your
example but it doesn't work.

could you breakdown what each of those references are?


"Tom Ogilvy" wrote in message
...
Assume the first table is in a sheet named A and the new table is in a
sheet
named B

Assume on the new sheet (B) Mon is in A2 and on the old sheet (A) Mon
is
in A1

in B2 of Sheet B

=INDEX(A!$B$1:$W1,1,MATCH(B$1,A!$B2:$W2,0))

then drag fill down and across

--
Regards,
Tom Ogilvy


"merlin" wrote:


I have a long table which goes up to 22 weeks of days. Each cell in
the
table refers to a shiuft (e.g. E1 early 1, M2 mid 2 etc). The numbers
at
the
top of the column are actually staff (1 might be Bill, 2 might be Ted
etc)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
1 Mon E1 O O O L1 L2 L3 O O L4 L5 L6 O O M1 M2 E2 O O O E3 E4
2 Tue E2 E1 O O O L1 L2 L3 O O L4 L5 L6 O O M1 M2 E1 O O O E2
3 Wed E3 E2 E1 O O O L1 L2 L3 O O L4 L5 L6 O O M1 M2 E1 O O O


What I'm trying to achieve is filling out a second table which shows
who
is
doing each shift on a particular day e.g.:

E1 E2 E3 E4 M M L1 L2 L3 L4 L5 L6
MON 1 17 21 22 15 16 5 6 7 10 11 12
TUE
WED


So I'm trying to find a function that says 'return the text at the top
of
the column in whichever column contains "E1" in row 1Mon" and then
again
for
"E2" etc and onwards for every day in 22 weeks.

It's a bit too complex for me so any help would be greatly
appreciated.