3rd drop-down list based on selection on 2nd one

[Mark Butler]

I am a 5th grade teacher developing a lesson plan template for myself & for my elementary work colleagues. We are now required to include the Common Core State Standards (CCSS) in our weekly lesson plans. To help with this task, I am working on a template using Excel wherein the teacher can look up common core standards. Here's how I would like the spreadsheet to function:Step 1: The teacher first selects a subject from a drop-down list:Reading Literature Reading Informational Reading Foundation Skills Writing Speaking and Listening Language Mathematics Social Studies ScienceFor example: The teacher selects 'Reading: Literature' from the first cell.Step 2: A second cell then displays the 'long form' list of standards based on the previous selection.For example:READING: LITERATURE CCSS.ELA-Literacy.RL.5.1: Quote accurately from a text when explaining what the text says explicitly and when drawing inferences from the text. CCSS.ELA-Literacy.RL.5.2: Determine a theme of a story, drama, or poem from details in the text, including how characters in a story or drama respond to challenges or how the speaker in a poem reflects upon a topic; summarize the text. CCSS.ELA-Literacy.RL.5.3: Compare and contrast two or more characters, settings, or events in a story or drama, drawing on specific details in the text (e.g., how characters interact). CCSS.ELA-Literacy.RL.5.4: Determine the meaning of words and phrases as they are used in a text, including figurative language such as metaphors and similes. CCSS.ELA-Literacy.RL.5.5: Explain how a series of chapters, scenes, or stanzas fits together to provide the overall structure of a particular story, drama, or poem. CCSS.ELA-Literacy.RL.5.6: CCSS.ELA-Literacy.RL.5.6 Describe how a narrator’s or speaker’s point of view influences how events are described. CCSS.ELA-Literacy.RL.5.7: Analyze how visual and multimedia elements contribute to the meaning, tone, or beauty of a text (e.g., graphic novel, multimedia presentation of fiction, folktale, myth, poem). CCSS.ELA-Literacy.RL.5.9: Compare and contrast stories in the same genre (e.g., mysteries and adventure stories) on their approaches to similar themes and topics. CCSS.ELA-Literacy.RL.5.10: By the end of the year, read and comprehend literature, including stories, dramas, and poetry, at the high end of the grades 4–5 text complexity band independently and proficiently.Step 3: After selecting one of these 'long form' standards, (Ex: 'CCSS.ELA-Literacy.RL.5.1: Quote accurately from a text when explaining what the text says explicitly and when drawing inferences from the text.'), the text for this standard is displayed in a second cell for viewing, and all the codes for the subject selected in cell 1 are accessible via a drop-down list in a third cell.Step 4: The teacher would then select the standard's code from cell c's drop-down list by matching it with the code that starts the 'long-form' standard in cell b.I have figured out how to use data validation with the indirect function to get cell c working, but am stumped on how to get the 3rd cell to populate based on the selection in cell b.I am using Excel 2011 for Mac, but would like this spreadsheet to work on a PC as well as a Mac. I have attached a link to a screenshot of the Excel spreadsheet I am currently working on.

Thanks for any suggestions!Mark Butler Springfield, IL​

 

[L. Howard]

Hi Mark, welcome to the forum.

I was tempted to pass on your post due to the huge amount of text I confronted. Your title pretty much says what you want, and I did not read the all massive narrative.

Clarity and brevity best.

Its nice you are a 5th grade teacher... Not relevant.
Its nice you are doing this for you and your colleagues... Not relevant.
Required to include the Common Core State Standards (CCSS) in our weekly lesson plans... Not relevant.

Try here, scroll down a bit until you see ADDING A THIRD DEPENDENT DROP DOWN.

You say you have two drop downs already, but you may want to take it from the top in the example shown to understand the concept better.

I admit, I struggle trying to get past two dependent drop downs and adding the third.


http://www.contextures.com/xlDataVal02.html

Regards,
Howard

 

[Mark Butler]

Points taken.

I didn't explain myself clearly in my initial post. I don't actually need a 3rd dependent list, but rather 2 separate dependent lists (cell b & cell c) that are both populated with 2 different (but related) data ranges based on range selected in the drop-down list of cell a. Is this possible?​

 

[L. Howard]

Hmmm, that is not clear to me.

Is there a chance you can post a link to a sample workbook and detail where the drop down go and what they are dependent upon along with other concise info.

I have good luck with Drop Box and there are others.

Howard

 

[Mark Butler]

Here is a link to the workbook: https://drive.google.com/file/d/0B2awmBcNyfAbeFpwdG5XRkZ2NFk/edit?usp=sharing

After selecting the subject in say P8, I would like R8 to be populated with a drop-list of the long-form standards for that subject and for cell N8 to contain a drop-list of all the codes for those standards listed in cell R8: i.e. just the first part of the standard.

So for example, I might select 'Writing' from P8, then drop down to 'CCSS.ELA-Literacy.W.5.3 Write narratives to develop real or imagined experiences or events using effective technique, descriptive details, and clear event sequences.' in R8. Then in N8 I would drop down to the abbreviated standard: 'CCSS.ELA-Literacy.RF.5.3' .

Mark

 

[Mark Butler]

Just to clarify, the 'Standards Look-Up' area of the spreadsheet is not a part of the print area- it's more of a reference tool.

 

[Mark Butler]

In my post #5 I meant to say 'CCSS.ELA-Literacy.W.5.3' and not 'CCSS.ELA-Literacy.RF.5.3'

 

[L. Howard]

Okay.

I'm building a clone workbook, if I can get it all in my head, of what you are trying to do. Making some progress.

Reason for a clone is the workbook in the link has a multitude of merged cells. Merged cells DO NOT work well at all with formula or vba code. I do not know how they react to drop downs and named ranges, and I have neither the knowledge nor the patience to try to work with them. They are also a huge trouble when it comes to trouble shooting.

OK for to look pretty in areas where there is nothing with code, formulas or drop down etc.

Howard

 

[L. Howard]

Question.

If R8 drop down is:

CCSS.ELA-Literacy.RL.5.5: Explain how a series of chapters, scenes, or stanzas fits together to provide the overall structure of a particular story, drama, or poem.]

Do you want this to show in N8?

CCSS.ELA-Literacy.RL.5.5:

Or do you want a choice of any of the below?

CCSS.ELA-Literacy.RI.5.1:
CCSS.ELA-Literacy.RI.5.2:
CCSS.ELA-Literacy.RI.5.3:
CCSS.ELA-Literacy.RI.5.4:
CCSS.ELA-Literacy.RI.5.5:
CCSS.ELA-Literacy.RI.5.6:
CCSS.ELA-Literacy.RI.5.7:
CCSS.ELA-Literacy.RI.5.8:
CCSS.ELA-Literacy.RI.5.9:
CCSS.ELA-Literacy.RI.5.10:

I'm guessing you just want the CCSS reference without the long text of whatever is in R8.
If that is true, then I believe its doable without the drop down.

Howard

 

[Mark Butler]

Yes, it would be better if the CCSS reference code alone were in the 3rd cell, rather than a list of all the codes for the standard selected in the first drop-list. This would save an extra step.

Thanks for your time.

Mark

Question.

If R8 drop down is:

CCSS.ELA-Literacy.RL.5.5: Explain how a series of chapters, scenes, or stanzas fits together to provide the overall structure of a particular story, drama, or poem.]

Do you want this to show in N8?

CCSS.ELA-Literacy.RL.5.5:

Or do you want a choice of any of the below?

CCSS.ELA-Literacy.RI.5.1:
CCSS.ELA-Literacy.RI.5.2:
CCSS.ELA-Literacy.RI.5.3:
CCSS.ELA-Literacy.RI.5.4:
CCSS.ELA-Literacy.RI.5.5:
CCSS.ELA-Literacy.RI.5.6:
CCSS.ELA-Literacy.RI.5.7:
CCSS.ELA-Literacy.RI.5.8:
CCSS.ELA-Literacy.RI.5.9:
CCSS.ELA-Literacy.RI.5.10:

I'm guessing you just want the CCSS reference without the long text of whatever is in R8.
If that is true, then I believe its doable without the drop down.

Howard