Excel Data Validation List Options

Question

I have encountered an issue with excel data validation. I have no problem setting up a drop down list from a table on a separate sheet, my issue is having the drop down list show a value that is different from what will be populated in the cell after making a selection. For example:

Image of table used in data validation drop down list

In the image provided, I would like the drop down list to show the value on the right (the actual department name), yet once a value is selected, to show the value on the left (the department ID). I have performed a lot of research attempting to resolve the issue on my own the past couple of days, but have had no luck.

Our problem is user's attempting to fill out the log will have to continuously check back and forth from a different sheet every time they wish to may an entry in the log. Providing the drop down list will enable our users to effectively enter their department ID, without having to memorize a list of numbers.

This is easy with either a macro or by using two cells ............are these approaches acceptable ?? — Gary's Student
I agree with Gary's Student, why not just have a second cell that does an Index/Match to lookup the department ID? Is there a specific reason the department ID can't be in a separate cell? — tigeravatar
I've never worked with a macro before, but I am open to anything that can help resolve the issue @Gary'sStudent — SamB.

Gary's Student Gary's Student · Accepted Answer · 2016-09-30T14:34:46

Put your DV-Dropdown in cell A1 and in B1 enter:

=IF(A1="","",VLOOKUP(A1,H2:I6,2,FALSE))

For this case:

You can adapt this approach to you own worksheets.

Excel Data Validation List Options

1 Answers