A cell follow the reference of another cell when using drop down

[tv_helge]

I am using a drop down on cell C3 on sheet 1 with reference to C 2 to C 50 in sheet 2.
In D3 sheet 1, I would like to reference whatever is in D 2 to D 50 in sheet 2, depending on which item I choose in the drop down on C3.

Example:

Sheet 2:
[TABLE="width: 500"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Paper[/TD]
[TD]USD 2[/TD]
[TD]Group 1[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Pen[/TD]
[TD]USD 5[/TD]
[TD]Group 2[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Eraser[/TD]
[TD]USD 3[/TD]
[TD]Group 2[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Tape[/TD]
[TD]USD 7[/TD]
[TD]Group 3[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 1:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Drop down of C 2-50, sheet 2[/TD]
[TD]"Auto-follow" D 2-50 depending on what you choose in C3[/TD]
[TD]"Auto-follow" E 2-50 depending on what you choose in C3[/TD]
[TD]"Auto-follow" F 2-50 depending on what you choose in C3[/TD]
[/TR]
[TR]
[TD="align: center"]3 (Example)[/TD]
[TD]Pen[/TD]
[TD]USD 5[/TD]
[TD]Group 2[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="align: center"]3 (Example 2)[/TD]
[TD]Eraser[/TD]
[TD]USD 3[/TD]
[TD]Group 2[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

When choosing "Pen" in drop down on C3 I would like D3 to show "USD 5", E3 to show "Group 2" automatically etc...

Is this possible? If yes, how?


regards
Helge

 

[Scott T]

Excel 2010[TABLE="class: grid, width: 300"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Pen[/TD]
[TD]USD 5[/TD]
[TD]Group 2[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Eraser[/TD]
[TD]USD 3[/TD]
[TD]Group 2[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="class: grid, width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD="width: 10"]Cell[/TD]
[TD="align: left"]Formula[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=VLOOKUP($C3,Sheet2!$C$2:$F$5,2,0)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]=VLOOKUP($C3,Sheet2!$C$2:$F$5,3,0)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=VLOOKUP($C3,Sheet2!$C$2:$F$5,4,0)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]=VLOOKUP($C4,Sheet2!$C$2:$F$5,2,0)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E4[/TH]
[TD="align: left"]=VLOOKUP($C4,Sheet2!$C$2:$F$5,3,0)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=VLOOKUP($C4,Sheet2!$C$2:$F$5,4,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[tv_helge]

Thank you for your really fast reply! However, I cannot seem to get it working. When using this, excel tells me there is something wrong with the formula. It highlights the "$C3,Sheet2", don't know if that means it is wrong though...
Any ideas?

regards
Helge

 

[Scott T]

I your second sheet named Sheet2? Some versions of Excel do not use the comma (,) in formulas but use semicolons (. If your version of Excel uses semicolons then change the commas to semicolons.

 

[tv_helge]

Hi, many thanks again! Semicolon helped Perfect!