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:
- (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.
- Slipped duplicates in the contents of a validity range may cause trouble if there are dependent cells. (See B009 in the attached example.)
- Long list of selectable items need a lot of scrolling.
- 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