CALC - Dynamic Drop-Down lists

[ChrisCrinkle]

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 only work if all the arguments are for the CURRENT SHEET of the spreadsheet?

The validity formula I used is: “INDEX($Data_Main.$B$3:$E$17,MATCH(B13,$Data_Main.$B$3:$E$17))”

[JohnSUN]

This is the second time you ask this question, but don’t show your document with sample data. It is not difficult to answer your question, there are several working options for solving this problem. Difficult to prepare datasets to demonstrate a solution. The formula in your question may be absolutely correct, but refer to the wrong cells in your spreadsheet.

[schiavinatto]

Sample file

https://wiki.documentfoundation.org/images/d/d7/Cidades_do_Brasil.ods

[schiavinatto]

See test file for the answer to this question.

Dependent dropdowns and dynamic named ranges

The Part Type column must be ordered.

[JohnSUN]

You chose such a difficult solution in vain - your task is solved much easier (three comments inside the spreadsheet) - Cidades_do_Brasil_JohnSUN.ods

Source ranges for Data Validity:

IF(xUFs=C4;xCidades;"")
IF(xEstados=E4;xCidades;"")

And condition for IF( "Cidade: ";"Acertar Cidade: ")

SUMPRODUCT(xUFs=$C$4;xCidades=$C$6)
SUMPRODUCT(xEstados=$E$4;xCidades=$E$6)

[schiavinatto]

@JohnSUN, I hadn’t imagined using such a simple formula, thanks.