Forum Discussion
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'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?
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.
16 Replies
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.
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.
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
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.
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 sent you a private message for the instructions.
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.
- 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, EvaThank 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...
