Lesson Plan Template: drop-down list in spreadshee

Question

I am a school teacher in the process of creating a lesson plan template for our elementary school using a Google spreadsheet. I want to make this template customizable for grades k-5 and also to incorporate drop-down lists of the Common Core State Standards (which we are now required to include in our weekly plans).

Here is a link to my progress so far:

https://docs.google.com/spreadsheet/ccc?key=0AmawmBcNyfAbdGozZHJObmlkcVpfdHRYQmlXU0JESmc&usp=sharing

Because there are different common core standards for each grade level, I would like the teacher to be able do the following:

1. Select her grade level from a drop-down menu
2. Select the subject (Math, Reading, Writing, etc.) from a drop-down menu
3. Populate a 'Standards' cell with the common core standard for the selected grade level/subject.

For example, a teacher first selects 'Fifth Grade', then Selects 'Math', and then is able to select the appropriate common core standard for 5th Grade Math: 'CCSS.Math.Content.5.NF.B.7 Apply and extend previous understandings of division to divide unit fractions by whole numbers and whole numbers by unit fractions'

Unfortunately, I have no knowledge of computer programming. I have spent several hours trying to search for a template containing this functionality and the closest I came to a possible solution was here:

https://docs.google.com/spreadsheet/ccc?key=0ApDwwZxW-IBcdGltVUplSUJydzhETjllMDNXZF85ckE&usp=sharing

So my question is, is there a way to incorporate drop-down lists in the way I have described using Google's existing spreadsheet functionality, or will I need to include some type of 3rd party script?

Mark Butler 5th Grade Teacher Springfield, IL

Answer 1

Yes its possible, and without using script code. Make the droplist by putting validation+from range. That range points to a range that has a =query cell formula. The query is made by filtering a master course table based on the cells already typed. Start with that.