[Solved] IF/THEN drop down with TWO dependant selections

Discuss the spreadsheet application
Post Reply
zivilyn99
Posts: 4
Joined: Tue Jul 25, 2017 9:00 pm

[Solved] IF/THEN drop down with TWO dependant selections

Post by zivilyn99 »

Hello,

Please excuse me if this has been asked before. I have been looking through these forums but can' t quite find what I am looking for.

I'd like to create an if/then style 'dynamic' drop-down whereby the first option is the input to two further drop downs, with choices dependant on the first option.

I have been using the 'colours' solution on this post so far:
viewtopic.php?f=9&t=58052
I like the 'colours' named lists solution as it's tidy, but happy to change if what i'm looking for if it can't be achieved.

For example, I need something like:
Initial drop down choices:
Colour:
Red, Green, Blue

Assuming 'Red' was chosen, a second drop-down of choices could be shades of Red, as per the original example:
Cardinal, Crimson, Fuchsia

And a third drop-down could have red-coloured foods, like:
Tomato, Strawberry, Cherry

The second and third drop downs are unrelated to each other - they both get their options from the first drop-down.

The trouble I'm having is that the dependant 'named list' needs to be exactly the name of the first choice. But you can only do that once. Surely there's some way of having 'Red' open up both the 'shades of red' and 'red foods' list?

Any help greatly appreciated.
Last edited by Hagar Delest on Thu Jul 27, 2017 12:13 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.1.3 on Windows 10
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: IF/THEN drop down with TWO dependant selections

Post by MrProgrammer »

Hi, and welcome to the forum.
Review in more detail the final post in that topic for December 2012 about "How to make validity list affect next cell list" where gerard24 explains how to use array references. If you can't understand how to use array references to achieve the desired result, you are more likely to get help with your specific data if you attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
zivilyn99
Posts: 4
Joined: Tue Jul 25, 2017 9:00 pm

Re: IF/THEN drop down with TWO dependant selections

Post by zivilyn99 »

MrProgrammer,

Thanks - I've had a play around and I can use the array reference method to achieve what I need.

A question though - is there a way to remove the top 'blank' space in the second/third drop downs? This also occurs in gerard24's drop-downs in the 'model' and 'colour' selections.

Other than that, perfect
OpenOffice 4.1.3 on Windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: IF/THEN drop down with TWO dependant selections

Post by Lupp »

@MrProgrammer : Supposing the above link was expected to lead to viewtopic.php?t=40651#p213602 I have a side-question.

The calculated 'Validity' ranges in the 'spate_list' example there are returned by incomplete calls to the IF() function like IF(Condition;ThenExpression) where the ElseExpression is missing. OpenFormula mandatorily specifies under "6.15.4 IF" this function to return the FALSE() value for the missing ElseExpression in such a case if the Condition comes out FALSE. Used in array mode with output to a cell range the mentioned expressions creating the 'Validity' ranges actually observe the specification. However the ranges applied for the cells subject to 'Data' > 'Validity' obviously contain empy text in replacement of FALSE.
In my humble opinion we shouldn't encourage users to rely on such devious details of the behaviour of specific tools. Whats bad with being explicit by making the IF expressions complete adding "" as the ElseExpression? Expense <0.2s . Since the behaviour of 'Data' > 'Validity' is not specified to any detail, its behaviour is to be expected subject to changes without notification insofar. (BTW: The ThenExpression also omitted 'Validity' lists the numeric 1 for it if applicable. If the ElseExpression is explicitly set to FALSE() we get numeric 0 for it... Don't tell me the behaviour of 'Validity is consistent insofar.)
zivilyn99 wrote:...is there a way to remove the top 'blank' space in the second/third drop downs?
I don't know such a way (except with the help of user functions) and in LibO up to the most recent versions 'Validity' behaves in the same way even if 'Allow blank cells' is disabled. As there is no public specification for 'Validity' we cannot prove this to be a bug. IMO it is one nonetheless.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: IF/THEN drop down with TWO dependant selections

Post by MrProgrammer »

zivilyn99 wrote:A question though - is there a way to remove the top 'blank' space in the second/third drop downs?
I use OpenOffice 3.2 and don't see the the top 'blank' space in dependent selection lists so I can't help with this question.
Lupp wrote:In my humble opinion we shouldn't encourage users to rely on such devious details of the behaviour of specific tools. Whats bad with being explicit by making the IF expressions complete adding "" as the ElseExpression? Expense <0.2s.
I agree with you. Personally, I would always use three operands for the IF function unless I specifically want to return the value FALSE when the test is not met.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: IF/THEN drop down with TWO dependant selections

Post by Lupp »

MrProgrammer wrote:I use OpenOffice 3.2 and don't see the the top 'blank' space in dependent selection lists so I can't help with this question.
That's strange. I actually tried with different versions. V3.2 is not at my disposition, however.
I would always use three operands for the IF function unless I specifically want to return the value FALSE when the test is not met.
In the mentioned specific case I would use FALSE() for the third parameter to put my intentions fully explicit.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply