Excel Dropdown with NO worksheet data list (with video)

Excel Dropdown
I built an Excel dropdown function — that requires no worksheet reference!

Reasoning

The dropdown built into Excel requires a range of data to populate the dropdown list. This might be an appropriate situation if the spreadsheet is a one-off. In my work, the dropdown list would more appropriately be generated in my PyXLL program.

Having the dropdown and its data provided by a PyXLL function allows:

  • central control of the list
  • easy updating
  • unrestricted placement of the dropdown — any spreadsheet, any tab, any cell

The function is written in PyXLL. The data for the dropdown is taken from a Python dictionary. The dialog is built using PyQt5: the dialog is a QInputDialog built using a QVBoxLayout.

The function allows:

  • a named reference
    Example: apples —> would reference my_dropdown_lists[‘apples’]
  • a regex filter for the dropdown list
  • an initial value for the dropdown list

PyXLL function information

PyXLL macro functions

Spreadsheet Setup

Dropdown Overview

Legend

Dropdown Legend

Assign macro to button

Dropdown Button

The ‘entry cell’ is shaded (for clarity only).
The optional ‘initial value’ entry is up one row from the entry.
The optional ‘regex filter’ entry is up two rows from the entry.
The optional ‘name reference’ entry is up three rows from the entry.

To set up the function, I assign my macro function to a button; then I label the button with text.

To use the function, I simply select a cell and click the button. The dropdown appears and I select the desired entry and click OK. On the first use, the ‘initial value’ is taken from the cell above. After each selection the cell above contains the last selection made.

After a selection, a new regex filter might be selected. If the last value becomes invalid then the initial value index position is set to zero (start of filtered list).

Dropdown in Action — Video

In the video, the default is ‘three letter names’ if there is no ‘name reference’ entry.

Regex example

Dropdown Button

The ‘regex filter’ entry is ^.*a.* which restricts strings to those with at least one ‘a’. Therefore, only these apples appear in the dropdown: ‘gala’, ‘macintosh’, ‘granny smith’, ‘pink lady’.

Python code excerpt




@xl_macro
def select_from_PyXLL_dropdown():
    """
    Execute a PyQt dialog based on a list from a dictionary
    """
    xl = xl_app()

    my_dropdown_lists = {
        "apples": [
            "gala",
            "honeycrisp",
            "macintosh",
            "cosmic crisp",
            "fuji",
            "granny smith",
            "pink lady",
        ],
        "countries": [
            "Uganda",
            "Ukraine",
            "United Arab Emirates",
            "United Kingdom",
            "United States of America",
            "Uruguay",
            "Uzbekistan",
        ],
        "three letter names": ["bob", "joe", "leo", "ned", "ray", "sam", "tom"],
        "cars": [
            "alpha romeo",
            "buick",
            "chrysler",
            "dodge",
            "elantra",
            "fiat",
            "gremlin",
            "honda",
            "isuzu",
            "jaguar",
            "kia",
            "lincoln",
            "mazda",
            "nissan",
            "opel",
            "pontiac",
            "quattro",
            "rolls royce",
            "saturn",
            "toyota",
            "uplander",
            "volkswagen",
            "wrx",
            "xantia",
            "yaris",
            "zagato",
        ],
    }

    r_entry = xl.Selection
    # next row up
    r_init_last_range = r_entry.GetOffset(RowOffset=-1, ColumnOffset=0)
    # next row up
    r_filter_value = r_entry.GetOffset(RowOffset=-2, ColumnOffset=0).Value
    # next row up
    r_named_variable = r_entry.GetOffset(RowOffset=-3, ColumnOffset=0).Value
...



Leave a Reply

Your email address will not be published. Required fields are marked *