Sub-category drop down menu

[Kalvinsmith18]

QTY	Description	Sub-category	Rate	Total
1	sprinkler		£	£
1	pipe		£	£
1	labour		£	£
1	design		£	£
1	hangers		£	£

<tbody>
</tbody>

So, I'm working on a spread sheet for work but hit a bit of a wall. I’ve got part of this spread sheet to work. This is just a small example of what I’m trying to do cause the proper version is going to be pretty big.

I got prices/rates to automatically appear depending onthe option selected in the drop down menu in the "description" using this formula:

=IF($D3="",0,VLOOKUP($D3,Sheet2!$C:$D,2,FALSE))

But what I didn’t realise was the spread sheet I made had one column for the item/material but in practice that list will be massive and difficult to find exactly what it is you’d be looking for. So I’ve added a sub-category but what I’m stuck with is selecting, for example,“sprinklers” as the main option in that drop down box and then that automatically narrows down the options available in the sub category to the table for sprinklers. This then would update the rate and price etc that would appear next to it. The table of sprinklers and other items would be listed on a separate sheet.

I tried to attach an example but don't know how to.



Cheers

 

[jmacleary]

Hi there. What you are after is a cascading (sometimes called dependent) drop-down. There are many help tutorials around, just google cascading excel drop-down. Here is one that I've used and its pretty good:https://trumpexcel.com/dependent-drop-down-list-in-excel/

 

[tyija1995]

Hi Kalvin,

The subcategories can be generated through INDIRECT and named ranges.

Name each of your description (category) tables as their description name (so go to the sprinklers table - highlight all the possible sub-categories and whack in a named range of "sprinkler")

Then when you raise a data validation rule, use a List type and "=INDIRECT(B2)"
Assuming B2 is "sprinkler" - then the options that will appear will be all the sub-categories in the sprinkler table! repeat for all the category tables I hope this makes sense! Let me know if you need assistance.

 

[Kalvinsmith18]

Hi John,

I tried copying the video best I could but it didn't seem to work but messing about with the ideas it was suggesting I got it too work.

When in the name manager and I add "DependantDropDown" the value changes to {...}and says error when I close. When I leave that out it seemed to work.

Thanks