Replace drop-down selection with another (adjacent) cell content

Question

Is it possible to replace an item selected in a drop-down list with information from another (adjacent) cell?

For example:

I have the list of items used in the dropdown on a separate worksheet (Sheet 2), and the selection by the user takes place on another worksheet (Sheet 1). 

Sheet 2:

Column A is named Text, Column B is named Code

Cell A2 is Construction, B2 is 5403

If a user selects an item from the drop-down list on Sheet 1 (Construction), can Excel replace it with 5403 (without VBA, which I don't know or understand)? 

Thank you for your help.

Answer 1

Thank you, teylyn. I don't know VBA, so I will leave my cells as they are. Appreciate the suggestions.

Answer 2

I understand what you want to achieve.

In your question, you said that you don't want to use VBA but what you want to achieve can only be done with VBA.

I was therefore suggesting an approach with a separate cell, which will work without VBA.

If you want to replace the selected value, then you need VBA. 

Are you happy to do that?

Answer 3

Thank you for the proposed solution. I want Excel to substitute the user's selection in the dropdown selection on Worksheet 1.

Currently the list of items used to create the dropdown is in Worksheet 2. The dropdown list uses the items from the Text column (Cell A2) on Worksheet 2, but I'd like it to be overwritten (replaced) with the Code from Cell B2 on Worksheet 2 which is 5403.

Thank you for any additional help you can provide.

Answer 4

Hello,

If an item is selected from a dropdown list, then the selected item will show in the cell. If you want to replace it, you will need VBA. If you don't want to use VBA, you can use a neighbouring column to look up the code and write it in the next cell. You can use a lookup function for this. If the dropdown is in cell A1, put a formula like this into B1

=vlookup(A1,Sheet2!$A$1:$B:$100,2,false)

This will find the text selected in the dropdown and will return the matching code.