How to create dropdown with multiple columns in excel

3
votes

enter image description here! Hi I am using Excel 2003. I need to add data validation to a column, it should display the two columns in the cell, when user selects, the 1st column value should be displayed in cell.

Ex:

Col1 Col2
IND India
CHI China

Column - display to select

Column
IND-India
CHI-China

after selecting CHI the China or CHI will be displayed in the cell.

Is there any possibility to do the process in Excel.

Thanks in advance for any help... enter image description here

3
excelexcel-formula

3 Answers

2
votes

(1)

If you need a dropdown item to display a different title after being selected, you can have another cell serve as the display. For example:

Dropdown Example

Where B3 (the helper cell) can have a formula like =LEFT($C$3,3) or use INDEX/MATCH to look up keys (like in your 'For Validation' table).

And where C3 (the data validation list) has a custom format like ;;;"(Change)" or "(Select)" or even a symbol like "↓", and will never display the actual selected list item.

You can also do this with VBA by creating an activex combo box control.

(2)

If you need one dropdown list to depend on the selection made in another, you can use dynamic dependent data validation: http://www.myonlinetraininghub.com/excel-factor-19-dynamic-dependent-data-validation

1
votes

Go to Devloper tab - insert - active control- insert comobobox draw comobox in your excel sheet right click on combobox, open property in listfill range, type range of your column set coloumn count = 2 change the widht of colomn

0
votes

You can use the dropdown menu for the first column and the VLOOKUP function for the second column.

VLOOKUP would look at the first column and based on what's there would fill the second with the data from somewhere else.

I've also found a YouTube tutorial on how to use the function. Here you go: https://www.youtube.com/watch?v=-WAEzokHSJM