SOLVED

Data validation lists - flow chart?

Copper Contributor

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'm trying to do is essentially create a list within a list, like a flow chart.

 

For example:

I'm creating a Maths specific tracker at the moment that has 3 dimensions of depth and specific focuses for each dimension. So if I select the dimension of depth as "Language and Communication" then I want a list of Language & Communication focuses to appear to be selected. But if I select the dimension of depth as "Conceptual Understanding" then I want a list of Conceptual Understanding focuses to appear instead. From there I want to be able to type in the action step as normal.

 

I've attached a very basic excel of what I'm looking for.

 

Ideally I'd like to have just one document with a different page for each teacher rather than loads of separate spreadsheets.

 

Is there a way that I can do this?

16 Replies
Hi, if you already know all about drop downs and especially about dependent ones, I would recommend performing with a more performing app like ms Access, relationships between tables, exact queries etc. otherwise I send you this tutorial.
https://m.youtube.com/watch?v=yychXPx2gUY


Greets, Eva

Thank you for responding!

 

I know about dependent data lists but I cannot for the life of me get them to work and I don't understand why! It's very frustrating.

 

I'm watching YouTube tutorials and I'm copying what they do EXACTLY, yet it's not working. I don't understand what I'm doing wrong.

Hi Phoebe,
I collude possibly help if you send some screenshots. First: Breathe...

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.

Sorry, I think I uploaded the original rather than the edited file.

best response confirmed by PhoebeRow (Copper Contributor)
Solution

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. 

Thank you Twifoo, that is exactly what I was looking for! I don't suppose you could explain how you did that, or point me in the direction of a tutorial for what you did?

 

I'd like to learn for myself as well so that I can recreate this with other subjects in future

I like the strategy that prevents the user from changing the 'dimension of depth' whilst the dependent category is non-blank.  I also note that your formula also accommodates varying length lists dynamically.

 

Where our objectives differ, is that I set out to eliminate all direct referencing and replace them with structured references or defined names.  I have come to be of the opinion that identifying a data value by its location relative to the formula [A1 or R1C1] is an abomination that has no place in serious calculation.  All the more irritating that it was done intentionally in VisiCalc "One could do it the programmer's way [using named variables] but that would be tedious".

 

p.s. I do realise that, in the main, Excel is used for purposes other than 'serious calculation'.  The fact that over 90% of spreadsheets are erroneous probably doesn't matter.

I sent you a private message for the instructions.

@Twifoo I'm struggling a little with this exact issue. Would you be so kind to share how you have done it with me as well?

I will forward the private message to you, tomorrow.

@PhoebeRow 

Though I had seen this problem before!  Now I can see whether I use the same methods from time to time; the answer being I do not.

 

Last time I used CHOOSE, this time a function I use only rarely, namely INDIRECT.  I notice that I also consolidated the Action lists into tables.  In the unlikely event of there being any calculation to aggregate the results this will provide a meaningful way of referencing statements.

 

Maybe @Twifoo is more consistent.  I will take a look.

We’ve been through this before. Perhaps, your second look, just like anything else, will be better than the first one. So do I.

@Twifoo I am running a similiar type thing, but going 2 levels deeper in validation, each piece based on the prior. Can you please send me the instructions on how to do this? Thank you

Hey @Twifoo - will you forward your information on dynamic dependent drop down list to me as well? 

@Morgan_Hickman

You may follow the steps in the attached file. 

1 best response

Accepted Solutions
best response confirmed by PhoebeRow (Copper Contributor)
Solution

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. 

View solution in original post