Ask Your Question
0

Cell Dependent Drop Down

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 close merge delete

1 Answer

Sort by » oldest newest most voted
1

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.)
all_ask67913_SecondLevelValidity_GeneralExample.ods

edit flag offensive delete link more

Comments

Thanks for the Information Lupp

Andrew Borg gravatar imageAndrew Borg ( 2016-04-08 13:50:57 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 2,083 times

Last updated: Apr 08 '16