[Solved] Is it possible to show diffrent droplists in a cell

Discuss the spreadsheet application
Post Reply
Fredrik Nilsson
Posts: 12
Joined: Fri Mar 29, 2019 9:51 am

[Solved] Is it possible to show diffrent droplists in a cell

Post by Fredrik Nilsson »

Hi!
Is it possible to show diffrent droplists in a specific cell depending on value in another cell?
If so! Someone who wants to help me with a solution?
Attachments
Selectable droplist example.PNG
Selectable droplist example.ods
(14.1 KiB) Downloaded 68 times
Last edited by robleyd on Tue Apr 09, 2019 1:51 am, edited 1 time in total.
Reason: Tagged [Solved]
Apache openoffice 4.1.1
Windows 10
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Is it possible to show diffrent droplists in a cell?

Post by Zizi64 »

You must use helper tables for the data range of each Data Validity cell. The values of the actual list will be choosen ased on the value of the condition Drop-down (A, B, C) by usage of the Vlookup() function.

In other words you will refresh dinamically the content of the cellrange of the Data validity cell - instead of the usage of static values.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Is it possible to show diffrent droplists in a cell?

Post by Lupp »

Zizi64 already hinted that the mode Cell range in the first tab of the >Data > Validity dialog may be named slightly misleading. In fact you needn't enter a constant range reference there, but can also give a correct expression resulting in such a range reference or an array of data. Besides the VLOOKUP() other lookup functions (HLOOKUP, LOOKUP) and the INDEX() function used with only the row or only the column parameter are capable of delivering an array, OFFSET() and INDIRECT() can return a range reference.
An expression selecting one of some (three in your case) array constants is also a (crude) way to achieve what you want. This closes the gap to the List mode under Criteria.
See attached demo.
aoo97617SpecialCaseOfCalculatedValidityRange_1.ods
(15.13 KiB) Downloaded 81 times
See also viewtopic.php?f=9&t=85196.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Fredrik Nilsson
Posts: 12
Joined: Fri Mar 29, 2019 9:51 am

[Solved] Is it possible to show diffrent droplists in a cell

Post by Fredrik Nilsson »

Zizi64 and Lupp! Thanks to both of you!
You guys really solved my problem in a good way.
Apache openoffice 4.1.1
Windows 10
Post Reply