• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 86
  • Last Modified:

Cascading Drop Downs

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
jmac001
Asked:
jmac001
  • 14
  • 10
1 Solution
 
Martin LissRetired ProgrammerCommented:
Please see this article and the referenced Contextures code.
0
 
Martin LissRetired ProgrammerCommented:
If you aren't familiar with VBA, let me know and I can implement the code for you.
0
 
jmac001Author Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Martin LissRetired ProgrammerCommented:
Will do but it might be several hours before I can get to it. I'll keep you informed.
0
 
jmac001Author Commented:
Thank you so much
0
 
Martin LissRetired ProgrammerCommented:
Working on it.
0
 
Martin LissRetired ProgrammerCommented:
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
 
jmac001Author Commented:
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
 
Martin LissRetired ProgrammerCommented:
This is tough but I'm still working on it.
0
 
jmac001Author Commented:
thanks for the update
0
 
Martin LissRetired ProgrammerCommented:
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
 
jmac001Author Commented:
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
 
Martin LissRetired ProgrammerCommented:
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
 
jmac001Author Commented:
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
 
Martin LissRetired ProgrammerCommented:
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
 
Martin LissRetired ProgrammerCommented:
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
 
Martin LissRetired ProgrammerCommented:
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
 
jmac001Author Commented:
Hi Martin, I'm taking a look at what you updated and will let you know.  Thanks
0
 
jmac001Author Commented:
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
 
Martin LissRetired ProgrammerCommented:
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
 
jmac001Author Commented:
I can consider this closed and open a new question if that will work for you.
0
 
Martin LissRetired ProgrammerCommented:
Yes, please do that.
0
 
jmac001Author Commented:
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
 
Martin LissRetired ProgrammerCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 14
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now