Cross Reference Vertical and Horizontal for Data

[Kev - Radio Man]

Hi,

I have a crew worksheet made up with a large number of cells. This sheet
contains the shift rosters for our workers, which runs 24/7, on a 4 crew
basis.
1st column is the date, 2nd day-of-week, and 3rd onwards are the crew and
their names (grouped)
1st Row is the crew, A,B,C,D.
2nd Row name of each operator.
3rd onwards is the day details, R=restday, D=day shift, N=nights, and any
leave details included.

I then use another sheet to grab information from the crew worksheet that
shows information for the crew, or the person.

Currently I am using "Vlookup" to look to the date required and then looking
the required number of cells across and get teh data in that cell.

What I would like ot do is say, I want the information (data) from the cell
which cross-references against a date and a persons name. This way if that
person is moved for whatever reason, a column is added or removed, it is
looking for the cross-reference not the lookup cell value.

Can this be done? If not is there another way to do it?
I have included a small copy of what I am trying to work with, the real
sheet has approx 40 columns across, and a few years down. Nearly forgot using
Excel 2003.

A B C D
KH AM PM BD
1/03/2008 Sat R N D R
2/03/2008 Sun R N D R
3/03/2008 Mon R R N D
4/03/2008 Tue R R N D
5/03/2008 Wed D R R N
6/03/2008 Thu D R R N
7/03/2008 Fri AL D R R
8/03/2008 Sat AL D R R
9/03/2008 Sun R N D R
10/03/2008Mon R N D R
11/03/2008Tue R R N D

Thanks for any help.
(e-mail address removed) or (e-mail address removed)

 

[Herbert Seidenberg]

Here are four methods:
http://www.freefilehosting.net/download/3dh09

 

[Kev - Radio Man]

Herbert,

Thank you for that file, this will get me started. I will have a look at
them and use the one that works the best for me.

Could I ask whether this can work with a crew detail. In the example you
sent it deals with a single operator. If I wish to look at a full crew (9
people), "A" crew, can it pull information from all operators on that crew.
In the example it would be row 1, with row 2 the name, and so forth.

Regards

Kevin.

 

[Herbert Seidenberg]

Added method #5:
List with Macro.
http://www.freefilehosting.net/download/3dhki

 

[Kev - Radio Man]

Herdert,

Thanks for the replies, it was a great help.
Kevin.