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

Discuss the spreadsheet application

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

Postby Fredrik Nilsson » Sat Apr 06, 2019 4:13 pm

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 12 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
Fredrik Nilsson
 
Posts: 12
Joined: Fri Mar 29, 2019 9:51 am

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

Postby Zizi64 » Sat Apr 06, 2019 6:14 pm

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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
Zizi64
Volunteer
 
Posts: 8224
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby Lupp » Sun Apr 07, 2019 7:18 pm

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 22 times

See also viewtopic.php?f=9&t=85196.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Postby Fredrik Nilsson » Mon Apr 08, 2019 10:56 pm

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
Fredrik Nilsson
 
Posts: 12
Joined: Fri Mar 29, 2019 9:51 am


Return to Calc

Who is online

Users browsing this forum: JeJe and 37 guests