Sub-category drop down menu

Kalvinsmith18

New Member
Joined
Jul 23, 2019
Messages
2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]QTY

[/TD]
[TD="align: center"]Description
[/TD]
[TD="align: center"]Sub-category
[/TD]
[TD="align: center"]Rate
[/TD]
[TD="align: center"]Total
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]sprinkler
[/TD]
[TD][/TD]
[TD]£

[/TD]
[TD]£

[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]pipe
[/TD]
[TD][/TD]
[TD]£

[/TD]
[TD]£

[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]labour
[/TD]
[TD][/TD]
[TD]£

[/TD]
[TD]£

[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]design
[/TD]
[TD][/TD]
[TD]£

[/TD]
[TD]£

[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]hangers
[/TD]
[TD][/TD]
[TD]£

[/TD]
[TD]£

[/TD]
[/TR]
</tbody>[/TABLE]
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top