Nested drop-down menu list

[Vin1]

Hi:

I am using LO 7.3.2.2 (x64) on desktop Win10 Home.

I want to fine tune a LO Calc file by adding a column that includes a Category drop-down menu, but I find that I might have need for subcategories, in some cases, to avoid a very long list of items.

I recall viewing a tutorial about what I am calling “nested drop-down menu list”, whereby if an option in the parent drop-down is selected, a second, a third, … etc. drop-down list is then opened, if they exist. For example, if one option is utilities, a second drop-down is opened showing, e.g. telephone, electricity, heating fuel, water, etc., as further, optional, selections.

Unfortunately, I cannot recall if the tutorial was for Excel or Apache Open Office or Libre Office.

Can somebody point me to a Libre Office video tutorial or within the LO Help that covers “nested drop down list”? “Nested drop-down menu list” is most probably an incorrect key word text string.

Thanks for reading this.

[FelipeAle]

Would be something like this ?

Condicionar o aparecimento de uma lista suspensa

Ola @FabioNetto, se “Listas Suspensas” for Dados de Validação, é isto que quer? Se a seleção for diferente de “FÉRIAS”, não se visualiza a segunda "Lista" [Descrição da imagem] Se for “FÉRIAS”, a segunda “Lista” fica visível…e alteravél [Descrição da imagem] [Descrição da imagem] Veja Arquivo do teste. Faça a seguinte alteração na Formatação Condicional da célula D3, onde esta B3, troque por ESQUERDA(B3;6), veja as imagens abaixo: ATENÇÃO: a grafia de FÉRIAS de ver sempre igual e estar …

Or this one?

CALC - Dynamic Drop-Down lists

I am trying to achieve this: https://www.contextures.com/xlDataVal02.html#TwoWord Where the 2nd drop down’s items is dependant on the first one’s selection. How do I do this in calc (also, my lists have spaces between words if that is important). I found an example here from another question: How can I create dynamic dropdown in Libreoffice Calc? The example file works when downloaded, but when I copy the exact same formula into my sheet it doesnt work (gives error:504). Does the formula onl…

[karolus]

…or may be this

[joshua4]

It is worth mentioning that @karolus’s solution (he’s referencing his own) is the forms control version of the German video referenced by @PKG in one of the links (Mehrere von einander abhängige Dropdownlisten /Auswahllisten (LibreOffice Calc) - YouTube) which uses data validation drop lists for the same basic effect. Both are very clever.
Neither would seem to handle having a non-leaf-node list that coincidentally has an entry of the same name as exists within another non-leaf-node list, short of an arbitrary change in one of the lists. However, in either case appending the parent list selection within the Python macro or before the INDIRECT() in the data validation reference field (and setting up named ranges accordingly) ought to disambiguate.

Flatten an array with data = (dat[0] for dat in data) in Python. Hmmm…maybe I’ll be won over to Python yet.

[karolus]

@joshua4

Flatten an array with data = (dat[0] for dat in data) in Python. Hmmm…maybe I’ll be won over to Python yet.

And that specific was a humble “workaround” before i was able to master this javaesque Api. the updatet Sourcecode:

from com.sun.star.beans import NamedValue

def update_other(button):
    """
    triggert by Event: 'Modified' of Listbox_button
    search for the Named_Range addressed by actually selected Item,
    and updates
    'other_button's ListEntrySource to the name_range…RangeAddress
    
    with credits to @ Villeroy:
    [increase Listboxes]
    (https://forum.openoffice.org/en/forum/viewtopic.php
    ?f=20&t=65127&p=289137&hilit=+ListEntrySource#p289074)
    """
    
    OTHERBUTTON = "List Box 2"
    
    control = button.Source
    item = control.SelectedItem    
    controls = control.Model.Parent
    doc = controls.Parent.Parent
    
    named_ranges = doc.NamedRanges
    name_range = named_ranges.getByName(item)
    
    name_val = NamedValue()
    name_val.Name = "CellRange"
    name_val.Value = name_range.ReferredCells.RangeAddress
    
    list_source = doc.createInstance("com.sun.star.table.CellRangeListSource")
    list_source.initialize((name_val,))
    
    for chield in controls:
        if chield.Name == OTHERBUTTON:
            chield.setListEntrySource(list_source)
            return

[Vin1]

My thanks to those that provided useful replies to my task. I am much closer now to solving .
I have learned that there is a difference between a “list box” & a dynamic/dependent drop-down list using Data>Validity>Allow Cell range/List… and still learning.

What I have called “nested drop-down menu list” is indeed related to using the Data>Validity path.

I will try out some of the suggestions given.

Thanks again.