We help IT Professionals succeed at work.
Get Started

Excel cascading dropdown creation

Last Modified: 2020-09-20
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.
Watch Question
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE