How to Create a Drop-Down List

Creating a Drop Down List for An Already Existing Data Table with Over 3500 Line Items (Rows)

You can select the cells in colum B (or the whole column) and then add a data validation rule that references the same column as a list.

See https://support.microsoft.com/en-us/office/apply-data-validation-to-cells-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249 for how to create a data validation.

Select the "list" option for the validation, then define the column B as the list. Make sure the check mark is set on "In-cell dropdown".

Then you'll have a dropdown list with the already present values in the column.

You can also add new values because they will become part of the list that validates the entries.

How to create a drop-down list in Excel?

First step is to make a list of the items you want to see in the drop-down, but anywhere in your spreadhseet , like so:

Then the next step is to click the "Data Validation" button in the ribbon :

Ok, step 3 is to click "Setitngs" in the "Data Valid" menu , and choose "List" from drop-drown:

Step 4 is to click on that little blue icon in the "List" sub-menu :

The next step is to simply highlight the Cells you needs.

Finished now. Here is dropdown :

How can I create a dropdown menu from a List in Tkinter?

To create a "drop down menu" you can use OptionMenu in tkinter

Example of a basic OptionMenu:

from Tkinter import *

master = Tk()

variable = StringVar(master)
variable.set("one") # default value

w = OptionMenu(master, variable, "one", "two", "three")
w.pack()

mainloop()

More information (including the script above) can be found here.

---

Creating an OptionMenu of the months from a list would be as simple as:

from tkinter import *

OPTIONS = [
"Jan",
"Feb",
"Mar"
] #etc

master = Tk()

variable = StringVar(master)
variable.set(OPTIONS[0]) # default value

w = OptionMenu(master, variable, *OPTIONS)
w.pack()

mainloop()

---

In order to retrieve the value the user has selected you can simply use a .get() on the variable that we assigned to the widget, in the below case this is variable:

from tkinter import *

OPTIONS = [
"Jan",
"Feb",
"Mar"
] #etc

master = Tk()

variable = StringVar(master)
variable.set(OPTIONS[0]) # default value

w = OptionMenu(master, variable, *OPTIONS)
w.pack()

def ok():
    print ("value is:" + variable.get())

button = Button(master, text="OK", command=ok)
button.pack()

mainloop()

I would highly recommend reading through this site for further basic tkinter information as the above examples are modified from that site.

Create dropdown list from data tab (conditional)

There are several ways to achieve this behavior, but I would suggest using the =FILTER function (you can read more about it here).

I have set up my mock Data tab as follows, but of course this method can be easily adapted:

You can see that I am listing all the ID-Category combinations and their corresponding value (I presume there are several Categories per ID).

---

Now to the main tab:

• For the ID column a simple Data Validation can be done. You can select all the IDs in the Data tab, the duplicates will automatically be thrown out. This can easily be achieved by:

  =Sheet2!$A$2:$A$7
  

• For the Category validation in the second column, an extra step is needed because natively populating drop-down lists (to dynamically adapt the Category drop-down to the current selected ID) is not [yet] supported. However, it can still be achieved will the following trick:

  

  Where the formula used in the helper for validation is the following:

  =TRANSPOSE(FILTER(Sheet2!B$2:B$7,Sheet2!A$2:A$7=A2))
  

  and in the catergory data validation we have the following range:

  =G2:2
  

• For the different values, we can again make use of the FILTER formula. You can paste this into the C2 cell and extend it to as may columns and rows as required:

  =FILTER(Sheet2!C$2:C$7,Sheet2!$B$2:$B$7=$B2,Sheet2!$A$2:$A$7=$A2)
  

Automatically Expand a Drop-Down List

You may try below codes.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
    If Target.Cells.Count = 1 Then
        If Target.Validation.InCellDropdown = True Then
            Application.SendKeys ("%{UP}")
        End If
    End If
End Sub

---

Related Topics