Dropdown selection that returns a different value based on selection.

[jmk15315]

Good morning.

I have been working to figure out the best way to accomplish what should be a simple task.

I have a spread sheet that column "J" is used to populate a code for the item selected. The issue is, that this code varies based on the category of the selection. The user knows this category by name, but our operations team knows it by a code. For example: Stands are code FG0700, Electrical Systems are code FG1000 and so on. I was hoping I could create a dropdown list by using Data Validation, but I am not able to have it return the correct value.

I would like for Cell "J" to have a dropdown that shows the name (Stands, Electrical Systems, etc...) and when selected it would populate cell "J" with the corresponding code (FG0700, FG1000, etc...)

I welcome any thoughts.

 

[etaf]

maybe a lookup table with the
names in 1 column and the code in the next column
but that would then return the result into a different column based on the dropdown
otherwise , likely to be VBA as you will have contents in the cell and want to change the result - i thought of choose / switch et

 

[jmk15315]

I tried creating a table and then pointed the dropdown to the column with the names, this works fine, but it does not populate the cell with the Code that corresponds with the name. It only gives me the name. I am not understanding how to have the dropdown list show the "Name" but populate the cell with the "Code"

 

[etaf]

as i mentioned , i dont think you can have the same cell change , without VBA , I maybe wrong

but you can populate another column as shown
A1 is the text, using datavalidation LIST and then in B1 you get the code from a lookup

Book2
	A	B	C	D	E	F	G
1	c	3
2						a	1
3						b	2
4						c	3
5						d	4
6						e	5
Sheet1

Cell Formulas
Range	Formula
B1	B1	=INDEX(G2:G6,MATCH(A1,F2:F6,0))

Cells with Data Validation
Cell	Allow	Criteria
A1	List	=$F$2:$F$6

 

[jmk15315]

I think you and I are on the same page. It may not be possible to have the "dropdown"cell with the "name" be the same as the cell with the value "Code".

I created a seperate "Selection" cell (Column P) to populate the name then used VLOOKUP to get the value from the table of codes and populate the cell in (Column J)

 

[etaf]

glad its solved for you - but as i say I wonder if VBA (I do not provide solutions on forums) may work as you wish