Multi tier dependant drop down

Hi Jon,

I've partially set this up for you in the attached file, and hopefully the explanation which follows will enable you to carry on with it.

You will see in column A of the Ref_data sheet that I have listed all the Elements, and I have set up a named range using the heading in A1 (to do this, you just select all the cells in the range, i.e. A1:A12, and then click on Formulas and in the Defined Names section choose Create from Selection - a dialogue box will pop up and you should ensure that Top Row is checked and click OK). This enables me to set up a data validation drop-down in cell A21 of the Template sheet using as the Source =Element.

I have then copied those individual categories of Element onto row 1 of the Ref_data sheet (well, the first 4 of them, anyway) and I've used a colour coding of white on a blue background for these sub-elements to help them stand out. Under each of these, I have listed the appropriate categories, so there are 4 types of Roof, two types of Walls, and so on. Then for each of those 4 types of Roof I have copied the cells into row 1 (and used a different colour coding again), so they can act as a named range for the Construction level of the hierarchy, so you can see there are 5 types of Flat Roof, and seven types of Pitched Roof, and so on. Each of these columns have been defined as named ranges, with the name in the top cell.

When it comes to Walls, you had Cladding as a category in both sub-elements of External and Internal, and as you can't use the same name twice I've changed these to Cladding_Ext and Cladding_Int, as these have their own list of sub-categories (Description). So from that, hopefully you can work out what needs to be done with the rest of your data.

Once that is all done, the rest of the Template sheet is already set up for you, with dependent drop-downs in cells B21, C21 and D21. These all use INDIRECT with the choice in the previous column (which is a named range) to give the appropriate choices in the drop-down, but there is one further consideration - spaces are not allowed within the names of named ranges, and Excel changes these to underscores automatically. So, we have to change any spaces to underscores and so the Source for these drop-downs is (in B21):

=INDIRECT(SUBSTITUTE(A21," ","_"))

and similar for C21 and D21. You need to ensure that there are no trailing spaces within your data on the Ref_data sheet. I've copied these DV drop-downs to row 145 of your table, so it is all set up for you. Note that I've only defined the 4th level of the hierarchy for Cladding_Int and Cladding_Ext.

Hope this helps.

Pete