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]

Okay, got a little clean up to do and will post a link to the clone I made.

Howard

 

[L. Howard]

Have a look see here. You may have questions, there is a list on sheet 1 explaining what I did.


https://www.dropbox.com/s/nezg2aaagrmqhkc/MDB Plan Clone Drop Box.xlsm

Regards,
Howard

 

[Mark Butler]

Hi Howard,

This is a neat solution! Thank you so much! Would you like me to share with you my finished template?

Do you know if there is a way to extend the width of drop-down lists? This would allow the teacher to read the entire standard, rather than just the first part of it.


Mark

Have a look see here. You may have questions, there is a list on sheet 1 explaining what I did.


https://www.dropbox.com/s/nezg2aaagrmqhkc/MDB Plan Clone Drop Box.xlsm

Regards,
Howard

 

[L. Howard]

Yes, I'd like to see the finished product.

For a wider drop down I suspect you will need to use a Combo Box.

Developer tab > Insert > In Active X controls > Combo Box.

I have very little experience using Active X controls, if you need help you can google Excel Combo Box use.

Lots of tutorials will show up, pick and choose.

Regards,
Howard

 

[Mark Butler]

Howard,

I will share when it's finished. Via email or in this thread?

I did find a simple solution for showing all the text in the drop-down:

https://drive.google.com/file/d/0B2awmBcNyfAbMUVWeFltLW9JRU0/edit?usp=sharing

I can make additional rows for the cut-off text. I'll have to include an input message: 'Choose the first row of text to ensure CCSS code is entered in Standards column.'

Thanks again for your help!

Mark

 

[Mark Butler]

Actually, a cleaner solution than the drop-down message is as follows:

https://drive.google.com/file/d/0B2awmBcNyfAbaTVTMy1iaVVDeDA/edit?usp=sharing

 

[Mark Butler]

I'm using Excel for Mac 2011. I don't see a Developer tab. Can I use Combo Boxes with Excel for Mac?

Yes, I'd like to see the finished product.

For a wider drop down I suspect you will need to use a Combo Box.

Developer tab > Insert > In Active X controls > Combo Box.

I have very little experience using Active X controls, if you need help you can google Excel Combo Box use.

Lots of tutorials will show up, pick and choose.

Regards,
Howard

 

[L. Howard]

I know nothing about Mac

I think this will get you close.

How to add data to a ComboBox or a ListBox in Excel for Mac

It mentions combo box and Mac and the page that comes up looks, from the title, to be just what you need


Howard