Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

This is possible if there is a well organised assignment of the varying second-level validity lists to the selectable first-level selected 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'.

This 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 selected 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'.

'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

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 selected 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