Calc formula for a double dependent drop down list

Elvanthyell · July 21, 2025, 5:27am

Hello

First and foremost, my Version information :
Version: 25.2.4.3 (X86_64) / LibreOffice Community
Build ID: 33e196637044ead23f5c3226cde09b47731f7e27
CPU threads: 8; OS: Windows 10 X86_64 (10.0 build 19045); UI render: Skia/Vulkan; VCL: win
Locale: fr-FR (fr_FR); UI: en-US
Calc: CL threaded

I have a very limited Calc knowledge and I am trying to create a double dependent drop down list (at least I think that’s how it’s called).

You can find a simplified version of my file attached (ultimately, there will be several sheets).

My goal is :

to associate Names (green row) with a number (blue row)
so that when a Name is selected by the user through a simple drop down list (orange cell)
the user can select a number, ranging from 1 to the number associated with the Name, through a second drop down list (yellow cell)

Grey cells contain the formulas used in the Data Validity (Cell Range) of the orange, yellow and purple cells.

I am using the following formula in the Data Validity (Cell Range) of the yellow cell, adapted from a youtube video :

IF(OFFSET(A2;0;MATCH(A4;B1:P1);ROWS(A2);1)=1;A10;IF(OFFSET(A2;0;MATCH(A4;B1:P1);ROWS(A2);1)=2;A10:A11;IF(OFFSET(A2;0;MATCH(A4;B1:P1);ROWS(A2);1)=3;A10:A12;IF(OFFSET(A2;0;MATCH(A4;B1:P1);ROWS(A2);1)=4;A10:A13;IF(OFFSET(A2;0;MATCH(A4;B1:P1);ROWS(A2);1)=5;A10:A14;IF(OFFSET(A2;0;MATCH(A4;B1:P1);ROWS(A2);1)=6;A10:A15))))))

The thing is, it seems to me the formula is only partially working.
Firstly, for the 4 or 6 first names (that are input in the orange drop down list), the numbers available in the yellow cell drop down list are correct.
However, for the rest of the names, the numbers in the yellow cell seem to either :

be quite random,
or acting like the first name was selected,
and sometimes the error code #N/A is returned,
futhermore, all of the above seem to somehow be connected to the Names → numbers vary when one changes a name and tries the yellow drop down list again.

I tried little variations in the formula (purple cells) but to no avail.
I also tried to use the IFS function rather than several nested IF, but it was way worse…

I hope my explanations (and my english) are good enough for you to help me !
If that’s not the case, please tell me what you need !

Have a good day

Double dependent dropdown Test.ods (17.4 KB)

karolus · July 21, 2025, 7:01am

Hallo

OFFSET($A$10;0;0;HLOOKUP($A$4;$B$1:$P$2;2;0))
# or better:
$A$10:INDEX($A$10:$A$15;HLOOKUP($A$4;$B$1:$P$2;2;0))

Additionally, if you only need a List of the first n numbers in the dependent drop-down:

SEQUENCE(HLOOKUP($A$4;$B$1:$P$2;2;0))    #since LO24.8

Elvanthyell · July 21, 2025, 1:49pm

Hi @karolus,
Thank you very much, both your formulas work like charms !
Why do you suggest that the second formula you gave is better than the first one ?
By the way, the third one is working perfectly too !

Do you have any clue as to why my original formula seems to act so randomly ?

koyotak · July 21, 2025, 10:52am

Question back: What is the relationship between the name and the number in each column? And what do you want to achieve with the number? The relationship of 15 names to 6 numbers is neither clear nor obvious — at least not to me. Please explain what should be restricted in the second selection [A5] as a result of the first selection “Name” [A4], as well as the subsequent restrictions in [A6] and [A7]. Is the selection in [A6] relative to what? Ditto in [A7]? The simplest approach for me is a logically structured list with given and sought values, as well as input columns and output columns, such as:
00000_Double dependent dropdown Test_125044.ods (24.4 KB)
I have implanted the last formula by @karolus .

Elvanthyell · July 21, 2025, 1:50pm

Hi @koyotak,
Thank you for your contribution !
To answer your questions :

I am using Calc to program a tabletop rpg. Each character has a power level, that they are able to modulate from one to their max level. Hence the number associated with their name, indicating their max power level.
The cells A6 and A7 were just other attempts at making my original fomula work, by tweaking it a little.

Knowing now that I only have 1 logical condition, do you still think that a logically structured list like the one you’re suggesting is the simplest approach ? It seems to me it’s a bit of an overkill, but I may be missing something ?

Do you have any clue as to why my original formula seems to act so randomly ?