Conditional Data Validation will allow the drop list in a cell to change based on the input in another cell.
The first thing we need to do is create named ranges of the main and sub categories.
Select the data in each list, but not the header. In the Name box type in the header.
Note: these have to be single words.
Follow the same process for all of the sub categories, these names need to match the main category exactly.
Now lets add the first data validation. Select the first cell click the Data tab and choose Data Validation.
Choose List from allow, check Ignore Blank and In-Cell Drop down
My main category is the named range Class so my source is "=Class"
Next lets create the Conditional Validation.
Choose the next cell, choose the Data Validation drop down.
Again choose List, this time for the source were going to use the Indirect formula
"=INDIRECT(A2)" and click ok.
Because A2 is blank you will get an error. The source currently evaluates to an error. Do you want to continue? Choose Yes.
Lets test it out, I choose Forested
Now the subcategory the list is updated with the Forested list.
To copy this validation down, select and copy the two cells with the validation.
Then Select all of the cells you want to copy it to.
Right click and select Paste Special, then Paste Special again
Choose Validation from the Paste Special List,
The Conditional Data Validation is now in all of the empty cells.
