Page 1 of 1

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

PostPosted: Sat Apr 06, 2019 4:13 pm
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?

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

PostPosted: Sat Apr 06, 2019 6:14 pm
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.

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

PostPosted: Sun Apr 07, 2019 7:18 pm
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 23 times

See also viewtopic.php?f=9&t=85196.

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

PostPosted: Mon Apr 08, 2019 10:56 pm
by Fredrik Nilsson
Zizi64 and Lupp! Thanks to both of you!
You guys really solved my problem in a good way.