How to create a Two-Column view from data validation list in Excel and then select a value to fill several cells of a sheet.

Question

Hi,

The following table "List of Roads" will have two fields to select their values from data validation lists which containing possible values (they are listed below). However, Data validation list in Excel enables a single column display and only its values can be selected.

What I need is to configure the Object Code field to display two columns (i.e. Object Code, Object Description) from "Object List". Then be able to select Object Code value based on desired Object Description. So for Line item 1, user can see the whole two-column display of "Object List", then can select the desire object code based on description. Similary, user can display the two-Column meaurement Unit list in all cells under Length Unit table column and select the desired length unit based on desire Unit.

Please note this is an example. but for a real case, I have the "List of Roads" table contain many fields with data validation lists. I tried creating combo box, but not practical because it has to complex.

I'm using MS Excel 2019, not 365.

Answer 1

Using a combo box would be the only way to do this. The built-in data validation drop-down does not offer the capability that you want.

Answer 2

Greetings Hans.

But this will be a cumbersome and time consuming. Thanks for your feedback.