It's something that would be useful to many, but the terminology used in Excel isn't as straight forward as many (non-accountants) would think.
Select any values in "Make" column and it'll restrict your choices in "Model" column to a certain range. In the famous words of Jeremy Clarkson, "how hard could it be?"
Actually, not terribly hard but time consuming the on the first try.
For this I've used Excel 2010, but the essential steps are the same in Excel 2003 except getting to the options will be a little different.
Setting up the category/subcategory list data
You'll have to map out all the possible drop-down values in the lists. It's best to put this on another spread sheet to avoid cluttering up your data.
So on the "Category" sheet, prepare your drop-down choices like so.
Primary category
Back on your data sheet, select the column for the primary list.
Then go to Data ribbon tab > "Data Validation".
Select "List" type for criteria and click on the data arrow to select your source.
Switch to the category sheet and select the cells you want for the primary list.
Now you're able to select the primary category on each row.
Naming cell groups
Next up is the preparing the sub-category data. For this, you'll need to group cells together and give them a name.
- Select the cells you're currently grouping (blue)
- The name for that group MUST match the corresponding label for the group in the primary list (red).
- Type in the name of the group in the green box area.
- Press enter to name them.
- Repeat this for all other cell groups (Honda, Toyota, etc)
If you made a mistake, use the "Name Manager" under "Formulas" to edit or delete them.
Related subcategory list
Going back to the data sheet, highlight the first cell you want to use as the sub-category.
Set up "Data Validation" with "List" validation type again, except this time you use a formula.
=INDIRECT(A2)
Since we're typing this into cell B2, we match the primary cell value to cell A2 (highlighted in yellow)
Once that's done, drag the corner down to apply this to cells in this column.
If there's an easier way, please let me know :(
Test if it works
If done correctly, it'll automatically populate the list items based on the value of the primary list.
And there you have it, related drop-down lists!
I think that's enough pictures for one post so I'll refrain from my usual post-mortem random GIF attachment.
