Nested drop down boxes to choose items in a named range - a better way?

[RockandGrohl]

Hi guys, I'm looking at improving a quote sheet we use. Essentially what our clients do is put their line items in that they are charging for, and then categorise them within our defined structure which exists in a sheet called "WBS". They use three drop-down boxes to categorise each of the line items.

This is the data looks like this in the WBS:

Vegetables	Large	Zucchini
Vegetables	Large	Cabbage
Vegetables	Small	Peas
Fruit	Large	Melon
Fruit	Small	Passionfruit
Fruit	Small	Orange
Dairy	Liquid	Milk
Dairy	Liquid	Cream
Dairy	Liquid	Milkshake
Dairy	Liquid	Sour Cream

What we have at the moment are named ranges which mean the first drop down list will be "Vegetables, Fruit, Dairy" - selecting one will only show the appropriate items in the second drop-down list - So Vegetables would show Large or Small, and selecting Small would only show you Peas in the third dropdown.


This relies on the using the Indirect formula in the drop down box wihch points to the WBS sheet named ranges, and that gets really fiddly really fast.

This is preventing us from alphabetising the WBS sheet, because it will muddle up all the named ranges. If we append data, we have to manually increase the WBS sheet, and finally the biggest problem is that we make updates to the master WBS sheet, but you cannot copy/transfer the WBS over to the template sheets we send out to clients. You have to kind of mirror changes in sync.


If this is the only way, then so be it, if there is a better way I'd love to know because this is fiddly enough as it is.


Thank you!