Multiple Lookups

[Jamie Dow]

Hi all,

I'm new to excel's advanced features and could really do with some help
on this one.

I have the dates for the coming month. I wish to lookup shift patterns
from another sheet.

Sheet 1 has the dates and a column for the results and column for shift
code (which I plan to hide)

We have a four week rotating shift with shifts labelled as such
M1
T1
W1
T1
F1
S1
S1
M2
T2
W2 etc. right up to S4

Question really is how do I get these shifts to be displayed on the
main sheet.

i.e. If I type in M1, I want that shift to be displayed, or M2 then
that shift. I cannot seem to find a straight answer for this.

I look forwards to hearing from you all, thankyou in advance,

Jamie

 

[duane]

Please describe what you want to do in more detail.

What do you mean by this?

i.e. If I type in M1, I want that shift to be displayed, or M2 then
that shift. I cannot seem to find a straight answer for this.

 

[Jamie Dow]

Well on sheet one I have the dates.
Sheet 2 are a list of who's working what shift. (this is a set 4 wee
rota)
That list on sheet 2 have a week and day number Week 1 Monday = M1

On the first sheet I want to be able to type in the code above i.e. M
and have the content from sheet 2 displayed next to a date.

Sheet one looks a like this at the moment;

01/08/2005
02/08/2005
03/08/2005

Sheet two looks like this

M1 C, L & I - Day :: A - Night
T1 A,L - Day :: I - Night
W1 L - Day :: I & C - Night
T1 A & L - Day :: I - Night
F1 C - Day :: L - Night
S1 A - Day :: L - Night
S1 I - Day :: L - Night
M2 C & L - Day :: I - Night
T2 etc.etc.

I want to (in column C say) on sheet one, type in the shift code i.e
M1 and next to the date on sheet one have the relevant person
working.

Sheet one would look like this

01/08/2005 C, L & I - Day :: A - Night M1
02/08/2005 A,L - Day :: I - Night T1


Hope this is clearer :

 

[duane]

=vlookup(c1,sheet2!a1:b1000,2.false)

asuming you type the code in C1 of sheet 1

 

[Jamie Dow]

I neglected to mention something yesterday (sorry)

Basically next month I would change the dates and then put in
different shift code.
So the dilemna now is will that vlookup do the same task?

I.e. in the third column first row, I might type in M1 this month a
it's first Monday of rota, it might be W2 Wednesday of second month i
that cell next month.


01/08/05 <shift> M1