Create Dependent Drop Downs Based off of User-inputed Data

[ibergmg]

Hello all,

I am trying to create a tool which will allow my clients to easily navigate through our on-boarding process; however, it's becoming a headache for me.

I'm open to VBA suggestions, I just need to know what to do.

Sheets:
CAT SUBCAT
FAULTS

Starting in in CAT SUBCAT, cell A2, the User will manually create/type categories for their enterprise.
In CAT SUBCAT, cell B2, the user will assign Sub Cats to their Categories.

There could be hundreds of rows of data.

IE:
COLUMN A: COLUMN B:
[TABLE="width: 355"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]BAKERY EQUIPMENT[/TD]
[TD] AIR BRUSH[/TD]
[/TR]
[TR]
[TD]BAKERY EQUIPMENT[/TD]
[TD] DOUGH DIVIDERS[/TD]
[/TR]
[TR]
[TD]BAKERY EQUIPMENT[/TD]
[TD] GLAZERS[/TD]
[/TR]
[TR]
[TD]BAKERY EQUIPMENT[/TD]
[TD] MIXERS[/TD]
[/TR]
[TR]
[TD]BUILDING AND GROUNDS[/TD]
[TD] ACCESS HATCH[/TD]
[/TR]
[TR]
[TD]BUILDING AND GROUNDS[/TD]
[TD] AWNINGS / CANOPY[/TD]
[/TR]
[TR]
[TD]BUILDING AND GROUNDS[/TD]
[TD] BENCH[/TD]
[/TR]
[TR]
[TD]COMMUNICATIONS[/TD]
[TD] PAGING SYSTEM[/TD]
[/TR]
[TR]
[TD]COMMUNICATIONS[/TD]
[TD] PHONES[/TD]
[/TR]
[TR]
[TD]HOT FOODS EQUIPMENT[/TD]
[TD] DEEP FRYERS[/TD]
[/TR]
[TR]
[TD]HOT FOODS EQUIPMENT[/TD]
[TD] HEAT LAMPS[/TD]
[/TR]
[TR]
[TD]PLUMBING[/TD]
[TD] BACKFLOW[/TD]
[/TR]
[TR]
[TD]PLUMBING[/TD]
[TD] DISPOSALS[/TD]
[/TR]
[TR]
[TD]PLUMBING[/TD]
[TD] DRAINS[/TD]
[/TR]
[TR]
[TD]PLUMBING[/TD]
[TD] EXTERIOR MAIN SEWER[/TD]
[/TR]
[TR]
[TD]PLUMBING [/TD]
[TD] FAUCETS[/TD]
[/TR]
</tbody>[/TABLE]

Once a user has input their data, I want them to create a dynamic lists based of the data.

On Sheet FAULTS,

I need FAULTS.A2 to have a drop-down list of of all unique values from CAT SUBCAT Column A, and FAULTS.B2 to have a dependent drop down based off the users selection in FAULTS.A2, so the user can then manually type in a fault code into FAULTS.C3.

I don't want the user to have to name ranges/lists, so I need to do this via formulas, or VBA.

I hope that this make sense.

Thank you for your help.

 

[SteveO59L]

have a look at Aladin's classic solution, here

http://www.mrexcel.com/forum/excel-questions/32-cascading-combo-boxes.html

 

[ibergmg]

Thanks, Steve; however, I don't see the answer to my question in that thread...maybe I'm missing it?

I'll try to break my problem down a bit more.

1. User opens my workbook to Sheet1, and manually types in their own Categories (Column A), and Sub Categories (Column B)
-There is no limit, or set range. Will be different for every client/user.
2. I need to name Unique Values from Sheet1.Column A as "Categories"
3. I need to link Sub Categories to it's parent Category for dependent drop downs.
4. On Sheet2.Column A, insert drop down of "Categories"
5. On Sheet2.Column B, have dependent drop down based off of Sheet2.Column A selection

The hard part is that I need it to happen automatically, based off the user's data they manually inserted.

Creating the category drop downs isn't a problem for me; where i'm struggling is getting Sheet2.Column B to correspond.

my initial thought is to somehow use a VLookup to return the Sub Category (Sheet2.Column B) drop down selections; but not sure if that's possible.

Thanks

 

[bs0d]

http://www.mrexcel.com/forum/excel-...te-userform-combobox-unique-values-range.html