Questions about dealing with drop down lists - data validation

[jmac1947]

Hello all,

Another in the "I have never done this before and don't know where to start" series

data validation and drop down lists?

I have a worksheet ("Constants") that contains a list of values that I want to reference in another tab via a dropdown list.
The length of list is constantly changing (adding new customers for example) however I can always calculate the start and the last entry row values for the list if I need to.
I have a data entry tab with a cell that has the "dropdown list" referenced via data validation. (say cell D3)

So far so good

Now my questions,
q1. can I always start the dropdown list display of options at the start of the range of names available, if so how
q2. is there a sort of "typeahead" feature that allows me to type a couple of letters and position the display at that point in the list of options.
q3. I finally select a name from the list and it is then displayed in say cell D3. Is there a "vba does the heavy lifting" option to know which row from the list of customers that the selection was made.

I am happy to post a work in progress workbook if that will help, I think these are general "how do I do "stuff" questions rather than specific to my workbook

I hope I have explained my questions adequately

Jmac1947 (and still learning)

[Ron Coderre]

Q1: Yes...you could use code like this to select the first item in the DV list:

Please Login or Register  to view this content.

Q2: There is no autocomplete in DV cells

Q3: The below function returns the position of the DV selection in the DV list
• ALT+F11...to open the VBA Editor
• Select your workbook from the project list
• Insert.Module
• Copy the below code and paste it into that module

Please Login or Register  to view this content.

Note: Replace "MyDVList" with whatever name you used for your validation list

If your DV cell is A2
use that function this way:

Please Login or Register  to view this content.

Q4: Yeah...you didn't ask about it, but...
If you put your DV List in a Table and assign a range name to the list items,
that range name will automatically expand and contract to accommodate the number of values in the Table.
Example:
In cells C2:C11

Please Login or Register  to view this content.

• Select C2:C11
• Home.Format_as_Table...select a format...Click: OK
• Select C3:C11
• Type a range name in the Name Box (to the left of the formula bar)
...eg MyDVList
• In your cell's DV settings
...Allow: List
...Source: MyDVList

Is that something you can work with?

[TMS]

To build on Ron's point number 4 which you didn't ask ...

If you make your list and convert it to a Table, it will be named something like Table1 (for the first Table created) and, in Ron's example, it will have a column header MyList.

You don't need to create a Named Range to refer to the Table as it already has a name generated by excel.

So, you can use:

Data Validation
A1:

Please Login or Register  to view this content.

Item Number
B1:

Please Login or Register  to view this content.

And you don't need any VBA ... though that's a nice little function.

See the example attached.

Regards, TMS

[jmac1947]

Hi Ron & Trevor.

Thanks guys for your responses, I think I can make some progress from here although the whole "Tables" thing is not something I ever needed to get involved with before so some research is required to fully understand your suggestions.

Ron, appreciate that it could be as simple as a cell formula, didn't think of that as I am deep into a macro driven solution and immediately thought of vba.

Thanks again guys, appreciate it when a couple of really experienced contributors take the time to assist

John (aka jmac1947)

[TMS]

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.