Solved

Cascading Drop Downs

Posted on 2015-01-06
24
75 Views
Last Modified: 2015-01-16
Hi Experts,

I am trying to create a calculator that will return dates based on selecting criteria.  I've tried the cascading drop down methods but I am not able to get the second criteria to pull in the drop down.  Also sure if this is the best way to go about getting the information that I need.

I am attaching a copy of my workbook.  The area that I am working on is the Production Sources.  I would like to select the Brand, Type and Partner.  How I envision the drop down working is once the Brand is selected, would see only the Type and Partner available for the Brand,  Then only the factory locations and vendors for the brand as well.  Is this possible to do? and Is it possible to get a unique list of values?
Indict-List-2015.01.05-v2.xlsx
0
Comment
Question by:jmac001
  • 14
  • 10
24 Comments
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Please see this article and the referenced Contextures code.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
If you aren't familiar with VBA, let me know and I can implement the code for you.
0
 

Author Comment

by:jmac001
Comment Utility
Hi Martin,

I know basic VBA, I don't think I know enough to be able to create the necessary macro that is in the article. Would appreciate your help.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Will do but it might be several hours before I can get to it. I'll keep you informed.
0
 

Author Comment

by:jmac001
Comment Utility
Thank you so much
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Working on it.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I've been unsuccessful in my effort to use cascading drop downs. The problem is that the method requires that there be a named range that has the name the previous selection. In other words it's required that there be a named range named 'BA' and another named 'WI'. If "BA" is selected it's required that there be named ranges named 'LM' and 'TR' which again is no problem, but if "WI" is selected then since the Types associated with that Brand are blank, then a named range with a blank name would be required and that's not possible.

If a VBA solution is acceptable then I can still probably help you but first let me make sure I understand what you want. My understanding is that if the user selects Brand BA that only data rows 2 through 20 should be shown, and then after that if TR is selected then only data rows 10 through 20 should be shown, and since all the Partners for a combination of Brand BA and Type TR are blank then that is the end of the selection process since that is the only choice.

If my understanding is correct so far would you like the user to be able to further narrow down the results by allowing him to choose, say, "Millwork (standard)" from the remaining visible rows, followed by, say, Factory "Ysia" followed by Vendor "Edwo" so that finally there is just one line showing (line 11)?
0
 

Author Comment

by:jmac001
Comment Utility
Thank you for the detail explanation on why the cascading method will not work.  If macros can be used that would be great and your assumptions are correct.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
This is tough but I'm still working on it.
0
 

Author Comment

by:jmac001
Comment Utility
thanks for the update
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
OK I finally have something for you so see the attached workbook.

Some things to note:

I changed the table on the PrSchTab sheet to a normal range of cells, and so now to filter the data, click on any of the salmon-colored cells (which in the workbook I refer to as "filters") and select  one of the listed values. I assume the filtering will be done left to right, but it should't matter. To start over click the 'Reset' button. The reset button says "Reset" but the cell behind it also contains the word "Reset" and I use that cell value to programmatically locate the line of filters.
The workbook contains a constant named MAIN_SHEET which currently has a value of "PrSchTab". If you ever change the name of that sheet then you'll need to change the constant's value.
I added a few named ranges. They all start with "Float_..." and with the exception of Float_Brand they are dynamic. In other words the number and content of the cells in the range change depending on what the user filters. I also added a sheet called Named Ranges for the contents of the named ranges. You should probably hide and password protect that sheet to avoid users messing it up.
Much of the filtering depends on Data Validation dropdowns and if you just want to select one of the dropdowns so you can change the data validation, you'll run into trouble because my code will be triggered. To prevent that from happening you should first run the 'Maintenance' macro. It's a toggle that will prevent/allow the dropdown from being displayed so when your maintenance is done you should run it again.
The dropdowns are enhanced through the use of a single, floating, ActiveX combobox as describe in this article of mine.
Q-28591457.xlsm
0
 

Author Comment

by:jmac001
Comment Utility
I believe that this work but I did notice that after I selected Brand = WI that the Type still had LM, TR available to select. This is blank for WI.

Also is it possible to link the drop downs to the the fields on Schedule Calc Tab the end goal is to populate the Production Lead Time?

G12 = Brand
G13 = Type
G14 = Partner
E17-E21 = Factory
F17-F21 = Vendor
G17-G21 = Production Lead Time
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Also is it possible to link the drop downs to the the fields on Schedule Calc Tab the end goal is to populate the Production Lead Time?

G12 = Brand
G13 = Type
G14 = Partner
E17-E21 = Factory
F17-F21 = Vendor
G17-G21 = Production Lead Time
I think that should be a new question but first please describe what you would like to have happen and process to make it happen.
0
 

Author Comment

by:jmac001
Comment Utility
As the user is selecting each of the filters the filters would continue to narrow down the results to be selected:

i.e.  If  WI is select for Brand.... On Type it would be blank, Partner would be blank and Aya.  If Aya is selected for Partner then for Factory only Erma would be available and vendor would be all results from F28:F32
If I were to select the following
Brand: WI
Type:
Partner: Aya
Factory: Erma
Vendor: Vati

End Result: Production Lead Time 42

Didn't include the category as this was intended for output on the Schedule Calc Tab and it was hard coded into the calculator, but I forgot to mention that in my initial question.
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
Here is the correction for the "I did notice that after I selected Brand = WI that the Type still had LM, TR available to select" problem. Let me think about what needs to be done for the additional processing that you'd like to see and I'll let you know if I'd like a new question.

First though, are you saying that you don't want to allow the user to filter on Category?
Q-28591457.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Another question. You say "If Aya is selected for Partner then for Factory only Erma would be available", however E28 is "n/a" and not "Erma", so what do you want to do about that? Can a user filter on "n/a"?
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
A third question. When you said
E17-E21 = Factory
F17-F21 = Vendor
G17-G21 = Production Lead Time
did you mean instead?
E16-E21 = Factory
F16-F21 = Vendor
G16-G21 = Production Lead Time

And if you find that I've resolved the problem you reported, please open a new question for these changes.
0
 

Author Comment

by:jmac001
Comment Utility
Hi Martin, I'm taking a look at what you updated and will let you know.  Thanks
0
 

Author Comment

by:jmac001
Comment Utility
Martin to answer your questions:

1. Are you saying that you don't want to allow the user to filter on Category?   for this exercise yes the user would need to be able to filter on category. However since I left out a piece of the criteria I'm not sure if you would want to send time on the filter.  The category is tied back to the categories on the Schedule Calc Tab that are static fields in the calculator that I'm trying to get to.

2. Another question. You say "If Aya is selected for Partner then for Factory...... Yes, if the selected Aya then from column E (Factory) they would see all values returned and it could be n/a.

3. Yes you are correct the first line starts in 16 and not 17.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I'm happy to make any and all changes you like (I actually have most of them done) but what do you think about my request for a second question?
0
 

Author Comment

by:jmac001
Comment Utility
I can consider this closed and open a new question if that will work for you.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Yes, please do that.
0
 

Author Closing Comment

by:jmac001
Comment Utility
Hi Martin,  thanks so much for your help so far.  I've opened the new request .... http://www.experts-exchange.com/Software/Office_Productivity/Q_28598152.html
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now