Maybe a Lookup Formula

[paulrm906]

Hello everyone
I have another formula that I really do not know where to start with, what I am trying to do is; In column "D" is the staff ID number and then in row "1" from column "O" to "S" is the date.
Sheet one
Column
D_________O______P_____Q_____R______S
ID No........1-10.......2-10.......3-10......4-10......5-10
21
22
23
24
25

Sheet two
is the staff graphic. with an example below.

Column
A...............B...........C..........D..........E..........F
ID ........1-10.....2-10.....3-10.....4-10.....5-10
21...........X..........X..........5..........4.........UP
22...........H..........H..........H..........H.........5
23...........5..........5..........4..........4.........X
24...........X..........X..........2..........2..........2

Now what I need to do is enter a formula in cell "O2" then match the ID number in D2 and the date in cell "O1". then look in the staff graphic in sheet two find the staff members ID number in column "A" and match the date in the first row. But in sheet one I only want to know if some one is rostered to "X", "UP" or "H" if it is not one of those then do nothing.
I would like to thank any one for any suggestions or help in advance and of cause I will be also sending a big thankyou to those that can help me solve this.

Paul
Moscow

 

[Aladin Akyurek]

Book8
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	ID No											1-Oct-06	2-Oct-06	3-Oct-06	4-Oct-06	5-Oct-06
2	21											X	X			UP
3	22											H	H	H	H
4	23															X
5	24											X	X
6	25											X	X
Sheet1

O2, copied across and down:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX({"X","H","UP"},MATCH(INDEX(Sheet2!$B$2:$F$5,MATCH($D2,Sheet2!$A$2:$A$5,1),MATCH(O$1,Sheet2!$B$1:$F$1,1)),{"X","H","UP"},0))))

 

[paulrm906]

Hello Aladin

Firstly thanks for your reply. I have been trying all evening to get your suggestion to work. I have made the changes that had to be made and have pasted the formula below as it now appears in my program. First off I should of mentioned that in sheet two (Graphic) every cell contains a formula. It is just a simple formula which is linked to the origanal graphic and so forth. and it is just the = to formula, example A1 in this program of mine = A1 in the origanal graphic. Secondly I did get the formula to work slightly by changing the range number from 300 to just 9 and it worked but it was giving me the result for the row 2 down. But when I again extended the range it would not work. Now I should also mention that some rows are empty. I don't know if thats makes any differance.
I hope this makes sense to you and again thanks for your help.

=LOOKUP(REPT("z",255),CHOOSE({1;2},"",INDEX({"X";"H";"UP"},MATCH(INDEX(Graphic!$CR$2:$GE$300,MATCH($D16,Graphic!$C$4:$C$300,1),MATCH(O$2,Graphic!$CR$2:$GE$2,1)),{"X";"H";"UP"},0))))

Paul

 

[Aladin Akyurek]

Hello Aladin

Firstly thanks for your reply. I have been trying all evening to get your suggestion to work. I have made the changes that had to be made and have pasted the formula below as it now appears in my program. First off I should of mentioned that in sheet two (Graphic) every cell contains a formula. It is just a simple formula which is linked to the origanal graphic and so forth. and it is just the = to formula, example A1 in this program of mine = A1 in the origanal graphic. Secondly I did get the formula to work slightly by changing the range number from 300 to just 9 and it worked but it was giving me the result for the row 2 down. But when I again extended the range it would not work. Now I should also mention that some rows are empty. I don't know if thats makes any differance.
I hope this makes sense to you and again thanks for your help.

=LOOKUP(REPT("z",255),CHOOSE({1;2},"",INDEX({"X";"H";"UP"},MATCH(INDEX(Graphic!$CR$2:$GE$300,MATCH($D16,Graphic!$C$4:$C$300,1),MATCH(O$2,Graphic!$CR$2:$GE$2,1)),{"X";"H";"UP"},0))))

Paul

=LOOKUP(REPT("z",255),CHOOSE({1;2},"",INDEX({"X";"H";"UP"},MATCH(INDEX(Graphic!$CR$2:$GE$300,MATCH($D16,Graphic!$C$4:$C$300,0),MATCH(O$2,Graphic!$CR$2:$GE$2,0)),{"X";"H";"UP"},0))))

 

[paulrm906]

Hello again Aladin
I have again tried your new suggestion and I still keep getting the results for the 2 rows above. I have looked has close as I can but can not see what is wrong.
And again thanks for your help and support.


Paul

 

[Aladin Akyurek]

Hello again Aladin
I have again tried your new suggestion and I still keep getting the results for the 2 rows above. I have looked has close as I can but can not see what is wrong.
And again thanks for your help and support.


Paul

Try to rebuild the exhibit I posted using the data you provided before applying the formula to your original data:

Book8
	A	B	C	D	E	F
1	ID	1-Oct-06	2-Oct-06	3-Oct-06	4-Oct-06	5-Oct-06
2	21	X	X	5	4	UP
3	22	H	H	H	H	5
4	23	5	5	4	4	X
5	24	X	X	2	2	2
6
7
Sheet2

Book8
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	ID No											1-Oct-06	2-Oct-06	3-Oct-06	4-Oct-06	5-Oct-06
2	21											X	X			UP
3	22											H	H	H	H
4	23															X
5	24											X	X
6	25											X	X
7
Sheet1

O2, copied across and down:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX({"X","H","UP"},MATCH(INDEX(Sheet2!$B$2:$F$5,MATCH($D2,Sheet2!$A$2:$A$5,1),MATCH(O$1,Sheet2!$B$1:$F$1,1)),{"X","H","UP"},0))))

This formula uses 1 in MATCH for your sample (Sheet2) is in ascending order.

Why don't you rebuild the foregoing in a separate workbook in order to get an idea how it's supposed to work before you implement it in your actual workbook?

 

[paulrm906]

Hello Aladin

Just want to let you know I finally got the above formula working and it was not working because I failed to notice one sentence of yours which was:
This formula uses 1 in MATCH for your sample (Sheet2) is in ascending order.
And then it was solved due to you helping icey_69 yesterday so again thanks very much.

Paul