Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Cascading Drop Downs

Posted on 2015-01-06
24
Medium Priority
?
84 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 10
24 Comments
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40533738
Please see this article and the referenced Contextures code.
0
 
LVL 49

Expert Comment

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

Author Comment

by:jmac001
ID: 40533847
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

Expert Comment

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

Author Comment

by:jmac001
ID: 40534109
Thank you so much
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40534660
Working on it.
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40535817
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
ID: 40536622
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 49

Expert Comment

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

Author Comment

by:jmac001
ID: 40538762
thanks for the update
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40539303
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
ID: 40541313
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40541326
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
ID: 40541357
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 49

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 40541481
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 49

Expert Comment

by:Martin Liss
ID: 40541496
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 49

Expert Comment

by:Martin Liss
ID: 40541587
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
ID: 40544933
Hi Martin, I'm taking a look at what you updated and will let you know.  Thanks
0
 

Author Comment

by:jmac001
ID: 40550041
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 49

Expert Comment

by:Martin Liss
ID: 40550060
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
ID: 40550078
I can consider this closed and open a new question if that will work for you.
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40550107
Yes, please do that.
0
 

Author Closing Comment

by:jmac001
ID: 40553725
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 49

Expert Comment

by:Martin Liss
ID: 40553833
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

610 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