AutoFilter on Multiple Drop Downs

[ccowman]

Hi everyone,


Brand new to the forum and have been reading Chandoo for a long time and it has immensely helped my Excel skills. I'm creating a big Dashboard for my client and am stuck with some VBA / sorting issues.


I have a data set (~5,000 rows / ~100 columns) and I have created drop downs for the user to dig deeper into the data set. I have 3 drop downs that I'm focused on allowing the user to change (Project Team Name, Release Date, Owner).


What I would like to have happen is the user select their team name (or "All") from the first drop down, the Release Date (or "All") from the second and the Owner (or "All") from the third. It would basically look like "Project #1"; "April 2014"; "John Doe" or "Project #2"; "All"; All".


I'm not exactly sure how to accomplish this via VBA (I have tried AutoFilter, but am not having any success). Any help or direction would be greatly appreciated. Thanks in advance for all the help!

 

[kaushik03]

Hi ccowman,


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended , and somebody surely will read your post and help you.


Regarding your question I would request you to upload your sample workbook here and clarify the below questions:

http://chandoo.org/forums/topic/posting-a-sample-workbook


1)Are the three drop downs will be dependent to each other?

2)After selecting three parameters(one from each dropdown) from dropdown, what all parameters you want to include in the report that should be generated?


Looking forward to your response.


Kaushik

 

[ccowman]

Thanks so much for the information, @kaushik03.


I have uploaded a sample worksheet to Dropbox (hopefully this link works properly) - https://www.dropbox.com/s/3dvo3s5xt2gofl4/_Requirements_Dashboard_Test.xlsx


What I'm trying to accomplish is to take the data from the "Requirements" tab and have them be placed in the "XTeam_Matrix" tab starting in cell B29. If the user selects "Project Team" = "CCA #1", while all other dropdowns are "All", every row from "Requirements" where Col E = "CCA #1" should be shown.


From there, the user can drill down into "Downstream Team", "Release Date", or "Owner". So potentially, the user would have all the data from the "Requirements" tab shown and then after drilling down, may only show a few results (if any).


I hope that helps clear things up and I look forward to gaining a better understanding from this community. Thanks!

 

[jason]

....welcome.....


...just to clarify, are you just wanting them to be able to select from the various options? (the different team neams, the 'release date', etc? as opposed to having them manually enter the data?


if so, Data Validation (oh, i dont do VBA lol) would be your tool. Select the cell(s), Data Validation, Allow 'LIST', then assign what choices the user can select from. This list of choices can be typed in the data validation window using the comma (,) to separate the different choices (Yes, No, Maybe) or it can exist in a cell range (A1:A12).


if this is not what you're looking for or are looking for something beyond this, please clarify.

 

[jason]

aahh... sounds more like Advanced filtering using a criteria range combined with the drop downs.... again, i can explain this via non-vba if you so wish.

 

[ccowman]

Hi Jason,


I already have the dropdown's setup with Data Validation and it's pulling the names from the "Project Mapping" tab.


Sorry if I wasn't clear before, but I'd like the user to select something from dropdown XTeam_Matrix!C22 and have the data from the "Requirements" tab be populated based on that choice. The user could filter even further by selecting a "Release Date" in XTeam_Matrix!C24.


Or the user could go the other way and only see the "Owner" by leaving XTeam_Matrix!C22:C24 = "All" and changing XTeam_Matrix!C25 = "John Doe".


Hopefully that clears a few things up. Thanks!

 

[SirJB7]

Hi, ccowman!


Just reading your requirements and wondering if this might help you:

http://chandoo.org/forums/topic/data-validation-list-automatically-shows-the-first-item-in-the-list


Regards!

 

[ccowman]

Thanks for the link, @SirJB7, but it's not quite what I'm looking for. That is for two drop downs that are dependent upon one another, while these drop downs are not related at all.


It's basically taking the Data->Filter ability, but moving the selection of it to drop downs, so the user doesn't have to scroll through all the columns to find the one they are looking for.

 

[jason]

would a picot table work?! if you're in 2010, showing the slicers could be really helpful as well...

 

[ccowman]

Thanks, @Jason, but unfortunately, not all users will have 2010, so that won't work like I want it to.


For all those that have read this, I have solved it in the most possible round about way, but it works. Basically, I created a separate for the first drop down and said if the value in the drop down matches what is in the cells, show it, else make it blank. After that, I just built off of that tab with the second drop down, then built off that with the third drop down. I have three extra tabs for all my calculations, but it works.