Avatar of Ted James
Ted James
 asked on

Excel cascading dropdown creation

I have looked at Excel tutorials on line and have not seen a good answer.  Hopefully Excel experts here can help.
I need to deliver a cascading dependent pull down matrix, with the availability for the user to expand on answers.  Two questions, one general and one specific example.
1.       General question.  All the tutorials point to using a “worksheet” to first create all the tables and then in the Validation process have the drop downs use these tables.  I would rather not have the “worksheet” as part of my deliverable that they can see, so either I need to “hide” the worksheet when it is finalized, or I would populate the drop downs manually.  Using a “worksheet” probably makes more sense, I know, but I don’t want it included in the spreadsheet delivered. Please advise.
2.      Using these steps please advise how to do the following example:
a.      Step 1.  First field drop down is choice of North or South.
b.      Step 2.  When North is selected, the choices are New York, New Jersey, Maryland, and other.  When South is selected, the choices are Texas, Florida and other.
c.      Step 3.  Starting with North as an example, if North and then New York is selected, the choices become Buffalo, Albany, Rochester and other.  If South was selected and then Texas was selected, then the choices become Houston, Dallas and other.
d.      In all cases where “other” is selected, the user is offered a blank, with some text that says for example “pick a preferred state” and then they can fill out whatever they want (unlimited size of that field).  Even if their answer in this field doesn't make sense.  Their choice.
e.      Also, I would like to give them the option of choosing more than one.  For example, if a drop-down includes Bergen, Newark, Trenton, Atlantic City, I would like them to have the ability to choose both Bergen and Trenton, and both show up in that field.

There are many more steps after this, but if you can just walk me through this part I can do the rest on my own.

Thank you.
* cascading dropdown* matrix* Excel TableMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
byundt

8/22/2022 - Mon
byundt

Also, I would like to give them the option of choosing more than one.
That one statement means you cannot use the Data...Validation list dropdown. You must instead use either Forms or ActiveX controls, either a listbox or combobox. Both of them sit on top of your worksheet, and are about as attractive as an AC adapter plugged into an electrical receptacle right next to an otherwise attractive piece of consumer electronics.

When I am setting up cascading dropdowns, I want to do so with the data on a worksheet. It's much easier for the spreadsheet designer to update the values presented in the dropdowns when they appear in worksheet tables than when they are hard-coded.

If you don't want the user to be able to tamper with the tables, make the worksheet xlSheetVeryHidden and it won't appear in the list when you rightclick a sheet tab and choose Unhide... To hide the worksheet that way, put a statement like the following in the Immediate Window in the VBA Editor and then hit Enter.
Worksheets("Sheet2").Visible=xlSheetVeryHidden

Open in new window

You could alternatively set the sheet visibility by changing its Visible property in the VBA Editor (provided you display the Properties Window).
ASKER CERTIFIED SOLUTION
byundt

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes