lookup two tables

[Titus]

Pressing Problem!

I've tried for the pass four days to figure out a way to obtain
information from two ranges and present the results either in a
separate userform or on the worksheet.

Can anyone help?

Problem: How do I determine what shift a particular worker is working
based on either the current date and/or a selected date?

Range one contains the compressed work week schedule, listing each
day..
i.e.,
CWW Schedule
M T W T F S S M T W T F S S
26 27 28 29 30 1 2 3 4 5 6 7 8 9
10 11 12 13 14 15 16 17 18 19 20 21 22 23
24 25 26 27 28 29 30 31 1 2 3 4 5 6
7 8 9 10 11 12 13 14 15 16 17 18 19 20

Shift Rotation
M T W T F S S M T W T F S S
A E E E X X X X X N N N N N N
B X N N N N N N N X X X D D D
C N X X X D D D D D D D X X X
D D D D D X X X X X X E E E E
E X X X E E E E E E E X X X X


If Robert is working "A" and scheduled to work the 29th. What
would he be working based on the current date or selecting a date. He
should be working "X" shift.

I can't figure out how to lookup two ranges and present the results.

Thanks for your help!
Titus

 

[Guest]

The first table is a bit of a problem for a lookup, since the same day number
appears multiple times (so if I know today is the 20th, which 20 do I use?).
But the only thing you really need to know is what day of the week it is and
whether that falls in the 1st week or 2nd week on the table. If you just
know the date that is in the upper left cell of the table (day AND month) you
can figure it out as follows (my formula assumes your table starts in cell
A1, that the date in the cell A2 is entered as a real dates(e.g. 4/26/06
instead of 26), and the date you want to use for the calculation is in P1):
=MOD(P1-A2,14)
this gives just the column number for the lookup.

To find the row, you need to match the shift in your second table (and the
MATCH function works well for that) - with me arbitrarily saying that this
range begins in A10 and the shift is entered in cell P2:
=MATCH(P2,A11:A15,0)
this gives the row number.
So now the lookup is:
=OFFSET(A10,MATCH(P2,A11:A15,0),MOD(P1-A2,14))

 

[Titus]

K Dales,

I tried what you suggested but couldn't get it to work. Would you mind
if I send you the worksheet showing you what I am trying to do?

Titus

 

[Titus]

Disregard my last message to you.

I worked with what you suggested and was able to get it to work...
....Thank You Very Much!!!

I now only have only more problem. Perhaps you can suggest a solution.
Now that I can determine what shift a worker is scheduled to work.
How do I now show the next seven days based on the selected date?

Titus

 

[Titus]

K Dales,

I spoke too soon. Your suggest partly worked, inthat, I'm able to sort
and bring down some of the matching data in the shift rotation, but it
seems that there is a problem with the 2nd & 3rd. row. It does not
copy over the information properly. I'm I doing something wrong?

Sorry, in my excitment, I jumped the gun.

 

[Guest]

I realized that I left one thing out of the formula: the column number is
offset by 1 (because on every multiple of 14 the MOD function is zero,
whereas I meant it to point to column number 1) - sorry, that was a bit
careless and is the most common mistake in trying to do something like this!
The real formula is:
=OFFSET(A10,MATCH(P2,A11:A15,0),MOD(P1-A2,14)+1)

For your other question (how to get a whole week to display): The only
thing you need to do is to change the date calculation to use P1+1, P1+2,
P1+3, ... so, for example, the 7th day (P1+6) would be calculated as follows:
=OFFSET(A10,MATCH(P2,A11:A15,0),MOD(P1+6-A2,14)+1)

 

[Titus]

Thanks,

It partly worked. My date table runs 5 weeks (35 columns) by 10 weeks
(10 rows), and the totation table runs 35 columns by 5 rows. I still
get the wrong calculations across my copy area.

I am using the following formula:
=OFFSET(D15,MATCH(N29,D1620,0),MOD(D29-A2,35)+2).

Is the problem in the length of the columns?

 

[Titus]

K Dales,

Thanks Again...

....this time I'm not jumping the gun. Your last two suggestions worked
prefectly, after I figured out what I was doing wrong (had wrong cells
holding incorrect formulas).

Thank you so much.

Titus