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

Mark Butler

New Member
Joined
Dec 15, 2013
Messages
10
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.
i4lEE.png
Thanks for any suggestions!Mark Butler Springfield, IL
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top