Feb 17 2019 02:05 AM - edited Feb 17 2019 02:39 AM
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?
Feb 17 2019 03:38 AM
Feb 17 2019 06:15 AM
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.
Feb 17 2019 08:20 AM
Feb 17 2019 03:37 PM
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.
Feb 17 2019 03:47 PM
Sorry, I think I uploaded the original rather than the edited file.
Feb 17 2019 09:16 PM
SolutionHello 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.
Feb 17 2019 11:19 PM
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
Feb 18 2019 03:12 AM
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.
Feb 18 2019 09:25 PM
May 03 2019 06:35 AM
@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?
May 03 2019 08:30 AM
May 03 2019 09:53 AM
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.
May 03 2019 10:07 AM
Nov 14 2019 10:27 PM
@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
Jan 29 2020 01:08 PM
Hey @Twifoo - will you forward your information on dynamic dependent drop down list to me as well?
Jan 29 2020 09:57 PM
You may follow the steps in the attached file.
Feb 17 2019 09:16 PM
SolutionHello 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.