exchanging values amoung sheets

[havey]

Hi everyone,

I have two sheets in a file that i trying to replace values in one sheet from the value lable in the other sheet.

Sheet 1 is like-> Question | CodeID | Label

question | 1 | 1
| 2 | 2
| 3 | 3
| 4 | 4
|5 | 5
|6 |6+
question2 | 1 |<=500
2 | 501-1000
3 | 1001-1500
4 | 1501-2000
5 | 2001-2500
6 | 2501+
question3 | 1 | Immediately
2 | Soon after
3 | Once common
4 | After a standard
question_last | 1 | Immediately
2 | Soon after
3 | Once common
4 | becomes standard

///Sheet2 has the column A values as its first row (minus the blank spaces) like so:

question | question2 | question3 | question_last

under each colum is numbers, so it looks like this:

question | question2 | question3 | question_last
1 | 4 | 1 | 2
3 | 3 | 3 | 2
4 | 2 | 3 | 3
6 | 5 | 2 | 4
2 | 1 | 4 | 3
6 | 2 | 2 | 1
5 | 6 | 4 | 3

So to recap sheet 1 is like this:
Question | CodeID | Label

and sheet2 is like this:
row of the Questions
column of values

Now for my question, how can i replace sheet2's column values for the lables in sheet1 that correspond to the codeID?

so Sheet 2 would end up looking like this:

question | question2 | question3 | question_last
1 |1501-2000|I mmediately | Soon after
3 |1001-1500| Once common| Soon after
4 | 501-1000 | Once common| Once common
6+ |2001-2500| Soon after | becomes standard
2 | <=500 |After a standard| Once common
6+ |1001-1500| Soon after | Immediately
5 | 2501+ | After a standard| Once common

Here is the actual file:

http://www.geocities.com/bella_cola/test.xls

Thanks!

[dmexcel]

F G H
Aswer 1 6 6+
Aswer 2 3 1001-1500
Aswer 3 6 After it becomes a standard drug
Aswer 4 4 After it becomes a standard drug

In column H I have the folowing codes

H1=LOOKUP(G1,B1:C6)
H2=LOOKUP(G2,B7:C12)
H3=LOOKUP(G3,B13:C16)
H4

[dmexcel]

F G H
Aswer 1 6 6+
Aswer 2 3 1001-1500
Aswer 3 6 After it becomes a standard drug
Aswer 4 4 After it becomes a standard drug

In column H I have the folowing codes

H1=LOOKUP(G1,B1:C6)
H2=LOOKUP(G2,B7:C12)
H3=LOOKUP(G3,B13:C16)
H4=LOOKUP(G4,B17:C20)

G1 is the answer for question 1 H1 is the result
G2 for question 2 etc.

[havey]

hi dmexcel,

thanks for the reply.

I having problems understanding your theory, i never use excel, and although your solution seem simple, i can't seem to implement it.

can you please explain simply for this newbie, sry i'm so dumb at this

[dmexcel]

E-mail me, I have your file, with your file and you will see what I am
talking about