Ask Your Question

Cell Dependent Drop Down [closed]

asked 2016-04-08 09:26:52 +0100

Andrew Borg gravatar image

With Libre Calc, is it possible for a cell to show a different set of values in a dropdown depending on a value of another cell?

Example: A Spreadsheet includes two columns - each one with a drop down. The First column is Make and the second column is Model. Is it possible to Change the values shown in the Model Dropdown depending on the value chosen in the Make dropdown?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-03 01:01:52.950550

1 Answer

Sort by » oldest newest most voted

answered 2016-04-08 11:19:21 +0100

Lupp gravatar image

updated 2016-04-08 13:48:38 +0100

Second-Level Dropdown :: Depending on Selection from First-Level Dropdown in another cell :: Data Validity

It is possible if there is a well organised assignment of the varying second-level validity lists to the selectable first-level items. You have to use the 'Cell range' mode of Data/Validity, and to enter a formula calculating a range reference (by OFFSET or INDIRECT) depending on the chosen first-level item into 'Source'.

Many questions to the same effect were asked (and answered) in this askbot site and also in some forums I visit on a regular basis. Lacking a generally used terminology we cannot easily find them. The term 'Dropdown' (also Dropdown, drop-down) is used in very different contexts. In this case it should read 'Data Validity Range'.

Trying to get to an answer more easily to find for future visitors, I enriched this text with a selection of related terms.
I also prepared a final(?) example which I attach here. It is for users who like to thoroughly study such an example. The lovers of Q&D solutions might better not use such a construct.

Anyway there are disadvantages of using the Data Validity feature. Some of them:
1. (Main) The user may be misled to believe in the validity of a once selected item even if it was deleted from the validity range meanwhile. Precautions depend on thorough design.
2. Slipped duplicates in the contents of a validity range may cause trouble if there are dependent cells. (See B009 in the attached example.)
3. Long list of selectable items need a lot of scrolling.
4. The validity setting for a cell is treated as a format property and may be copy/pasted elsewhere inadvertently. (Not too serious.)

edit flag offensive delete link more


Thanks for the Information Lupp

Andrew Borg gravatar imageAndrew Borg ( 2016-04-08 13:50:57 +0100 )edit

Question Tools

1 follower


Asked: 2016-04-08 09:26:52 +0100

Seen: 2,589 times

Last updated: Apr 08 '16