Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Multi Column Data validation List

0

How to use multiple columns (2 or 3) in ONE dropdown list created with Data Validation.

Not macro or VBA

Answer
Discuss

Answers

0

Data validation doesn't have multi-column functionality. Multiple columns in drop-down lists require the use of combo or list boxes.

On the other hand, what do you want with several columns? Having several columns usually requires VBA to access them an utilise the information they contained. Since you specifically want to avoid VBA chances are that what you have in mind could be realised in some other way, perhaps even better. Care to elaborate?

Discuss

Discussion

Thank you for your response. I was actually expecting that it would not be possible. I am sort of a novice to Excel and do not know VBA. The reason I want  2 or 3 columns in ONE dropdown is that I wanted to avoid a very long list that requires scroll down. All names in three shorter lists are visible for the user on one worksheet whilst  the bottom end of ONE long list is not visible. Will combo boxes to the trick? If so, how?
Thank you for your assistance
Siyas (rep: 2) Mar 24, '18 at 2:00 am
No. Multi-column combo or list boxes don't imply presentation of one list in multiple columns but multiple information for each item on a list, like Name, Adresse and Number. The feature is useful where the user selects a name from the list and sees address and number displayed elsewhere.
A ComboBox can "jump" to a position in a long list when the first character is entered, potentially avoiding a scroll. Otherwise, consider using a series of DV lists with changing lists, such as one drop-down selecting "Boys" or "Girls" and the other automatically filling up with the chosen pre-selection.
How do you supply the list to the DD? Defining the list like =Lists!$A$1:$A$25 has an advantage over squeezing 25 names into a DV specification itself as well as enabling the keeping of the list on a dedicated, hidden, worksheet. Using a named range instead, like =MyList, allows you to manage the list without needing to modify the DV.
Either way this should take care of your wish to have only a partial list on another worksheet. That other DD could draw on the same actual list but it is an independent DD nevertheless. It is possible to give different names to overlapping ranges. For example, if Lists!$A$1:$A$25 is called "MyList", Lists!$A$1:$A$13 could be called "Boys" and Lists!$A$13:$A25 "Girls" (with a nod to LGBT lol:)
Variatus (rep: 4889) Mar 24, '18 at 4:00 am
Add to Discussion
0

Daddy knows best !

Discuss

Discussion

C'mon, you could just throw-in a helpful answer instead of stirring the pot ;P
don (rep: 1989) Mar 24, '18 at 5:35 am
I asked to delete my account, no response yet...
pecoflyer Mar 24, '18 at 10:45 am
What I currently have, and don't like, is a Named Range, but the cells in the Named Range are blank and needs to be populated by the user. Now when I populate and go to the dropdown it starts at the last name and shows the blank spaces.
What I would like, now that three shorter columns have been ruled out, is a Dynamic Range. In other words, as the user populates the list, the dropdown will expand, BUT always start with the first name in the dropdown. Will this be possible? Thank you for reading and contributing to solve my problem. With each problem (and answer of course) my knowledge of Excel grows.
Siyas (rep: 2) Mar 25, '18 at 2:48 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login