Forum Discussion

PhoebeRow's avatar
PhoebeRow
Copper Contributor
Feb 17, 2019
Solved

Data validation lists - flow chart?

Hello, I'm a school leader and I'm trying to create an Excel spreadsheet to track Teaching & Learning action steps that I give to teachers.   I understand how to create a drop down list but what I'...
data validation
Dropdown Lists
excel
flowchart
  • Twifoo's avatar
    Twifoo
    Feb 18, 2019

    Hello Phoebe,

    What you need is a dynamic dependent drop-down list. In the attached file, I inserted Lists sheet. You can add (or delete) categories of Dimensions of Depth and Dimension Focus. There is no more need to define additional named ranges. 

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Feb 17, 2019

First use the Dimensions of Depth column to give a validation list for Tracker column B.   I use defined names throughout.  Then use MATCH to determine which dimension was selected from the validation list by the user.  I then defined the dependent validation list, 'Validation', to refer to one of the ranges

= CHOOSE( DimensionNumber, LanguageComm, ConceptUnderstanding, MathThinking )

[see Name Manager].

The apply the dependent validation to the cells in column C.

 

Note: If you then change the dimension cell, the focus will be inconsistent.  It is worth using conditional formatting to flag the error.

Resources