Multi tier dependant drop down

Hi,

I am trying to create a spreadsheet which usues multiple drop downs. Each DD will need to provide differnnt options depending on the previous one selected. The photo below shows the columns and some of the data I need.

Table.jpg

I have also attached the main file if this helps.

I am trying to make it that depending on what I select in 'Element' will alter what available for selection in 'Sub-Element' which will alter what is available in 'Construction'.

Hopefully someone can help.

Thanks in advance, Jon.

You can do this by setting up named ranges for each sub-element which use the name of the element chosen, and for the construction which use the names of the sub-element chosen. I can show you how to do this, but where do you want the drop-downs to be located?

Also, it is not a good idea to have blank rows between your data.

Hope this helps.

Pete

Hi Pete,

Thanks for your reply.

Yes if you could provide a bit more guidance into that I woukld greatly appreciate.

All of the drop downs need to be in the main sheet with the feed in data in a separate sheet which I will hide. This should allow me to just duplicate the rows and select each element as I need.

I hope this answers your question but please come back to me if you need more.

Thanks, Jon

In your Block A & Site sheet you have a lot more Elements shown than in your Formular (sic) sheet. Do you want me to set it up with the Elements and sub-Elements from the first sheet?

Pete

Hi Pete,

Thank you for your assitiance so far.

I have now updated the excel document which I have attached hopefully making life easier for you. I have deleted everything from the Template sheet and put the required inputs into the Formula sheet (spelt correctly this time).

I would like to include costs at some point to auto populate when the priority is 2 or less but I still need to put a costings sheet together. If you have any thoughts about the best way for this please let me know.

Thanks, Jon

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

Hi Pete,

Thank you very much for what you have done.

I was having a play with it yesterday and seem to have grasped the concept, albeit rather loosly. What you have provided fills in some gaps and sets it out much cleaner than what I was doing. So yes I should be able to apply this accross the rest of the document. Again thank you for your time and guidance.

Thanks, Jon

Glad to hear that, Jon, and thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete