Searching for answer involving drop down box..

[Bravo2003]

OK, think this one may take some resolving...


Sheet 3 Column F needs to have a formula that will:

1. Look in cell E2 (drop down cell choice A, B or C)
2. Also look at Cell in column D (same row as F) for unique item code
3. Look in sheet 2, find matching item code in column D (same column sheet 2 & 3)
4. Then dependent on A, B or C in cell E2
   1. return answer from A=Column G, B=Column M and C=Column T

For test purposes lets work on ROW 4 to start

So Sheet 3 has - Cell D4 = 01237, & E2 = B need answer in F4
Sheet 2 has - Cell D4 = 01237, G4 = 69.06, M4 = 35.49 & T4 = 33.12

If it helps Unique Item code appears line up in both sheets i.e: D4 is same item, D5 is next item, etc...

Anyone able to offer a solution?

Thanks in advance.

Regards

Bravo

 

[jasmith4]

=INDIRECT("Sheet2!"&CHOOSE(CODE($E$2)+1-CODE("A"),"G","M","T")&ROW(D4))

1. The CHOOSE converts A/B/C into 1/2/3 using CODE($E$2)+1-CODE("A") -- CODE returns the ASCII value of a character.

2. Using the 1/2/3 it chooses "G", "M" or "T", then the row number of D4 is added on, forming "G4", "M4" or "T4".

3. Since the numbers are on Sheet2 it prefixes the above with "Sheet2!", forming "Sheet2!G4", "Sheet2!M4" or "Sheet2!T4".

4. Finally, INDIRECT translates that string into a formula, yielding your result. Note: no initial equal-sign (=).

 

[Bravo2003]

=INDIRECT("Sheet2!"&CHOOSE(CODE($E$2)+1-CODE("A"),"G","M","T")&ROW(D4))

1. The CHOOSE converts A/B/C into 1/2/3 using CODE($E$2)+1-CODE("A") -- CODE returns the ASCII value of a character.

2. Using the 1/2/3 it chooses "G", "M" or "T", then the row number of D4 is added on, forming "G4", "M4" or "T4".

3. Since the numbers are on Sheet2 it prefixes the above with "Sheet2!", forming "Sheet2!G4", "Sheet2!M4" or "Sheet2!T4".

4. Finally, INDIRECT translates that string into a formula, yielding your result. Note: no initial equal-sign (=).

Thats perfect, but I need to rename sheet2 to be 'CTF Spreadsheet', but when I change it it's messing the formula up?

What do I need to do?

 

[jasmith4]

Put the sheet name in single quotes:

=INDIRECT("'CTF Spreadsheet'!"&CHOOSE(CODE($E$2)+1-CODE("A"),"G","M","T")&ROW(D4))

 

[Bravo2003]

Your a star.... thanks.