Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross Reference Vertical and Horizontal for Data
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. or |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross Reference Vertical and Horizontal for Data
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross Reference Vertical and Horizontal for Data
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" wrote: Here are four methods: http://www.freefilehosting.net/download/3dh09 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross Reference Vertical and Horizontal for Data
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross Reference Vertical and Horizontal for Data
Herdert,
Thanks for the replies, it was a great help. Kevin. "Herbert Seidenberg" wrote: Added method #5: List with Macro. http://www.freefilehosting.net/download/3dhki |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Turn vertical data to horizontal data automaticaly | Excel Worksheet Functions | |||
Need lookup of data both vertical and horizontal | Excel Discussion (Misc queries) | |||
transposing vertical data to horizontal with varying amount of data | Excel Discussion (Misc queries) | |||
How to make Horizontal data go Vertical | Excel Worksheet Functions | |||
tranfere vertical data to horizontal tabel | Excel Worksheet Functions |