Solved

Filters for Calculator

Posted on 2015-01-16
16
65 Views
Last Modified: 2015-01-22
I would like assistance with creating several filters that act as a drill down.  In the workbook that I am attaching all of the data resides on the PrSchTab. I would like to take the data from the PrSchTab and be able to use to filter for information in the Production Source area on the Schedule Calc tab.

Starting with column labeled Brand, once the brand is selected would only like to see the Type that is associated with the brand this can include blanks.     Once the Type is selected would then only like to see the Partner (field can be blank) then from there the Factory and Location can be selected, however these are dependent on the category which is hard coded into the Production Source calculator Lines D16:D2.

The end result is to get the Production Lead TIme number so that a date can be calculated and populated in column G on Schedule Calc Tab

I have comments on the attached workbook.

Please let me know if there are additional questions.
EE-Help-2015.01.16.xlsx
0
Comment
Question by:jmac001
  • 9
  • 7
16 Comments
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
Try this.
Q-28598152.xlsm
0
 

Author Comment

by:jmac001
Comment Utility
Hi Martin getting a run time error 32809 when debugging it is at Function TheFilterRow. Highlighted in yellow For each rcell...
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
It doesn't happen to me so please describe the exact steps that led to that error. What version of Excel do you use?
0
 

Author Comment

by:jmac001
Comment Utility
I am using 2010.  When I select all of the criteria (all cells in orange) and then click the update button.   Also received an error when I tried to reset (same error number as above)
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
After selecting the criteria in each of the orange cells that you want to filter, just press one of the two yellow buttons. No need to select the orange cell's first.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Here's a quicktime movie of me doing it in Excel 2010. Change the extension from jpg to mov.
Screen-Recorded2015-01-16-13-39-05-.jpg
0
 

Author Comment

by:jmac001
Comment Utility
Ok now that I see what is suppose to happen the data is not drilling down at all when I select the first filter all of the data is still in the table (BA & WI).  

Is there anyway to take the information and have the drill down information populate on the first tab?
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I don't know what's going on at your end but it works for me. Here's a picture and you can also see it happening in the movie I posted.
Works for me
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:jmac001
Comment Utility
I am working to see if I can identify why the code is not working
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Did you happen to save the file in some format that doesn't save the code (like xlsx)? You can tell by going to Visual Basic and seeing if there's any code there. You could also download a fresh copy of my attachment and see if it works. If you still have a problem after that please describe, step by step, what you are doing.
0
 

Author Comment

by:jmac001
Comment Utility
Still having problems I've downloaded the files a couple of times and tried to filter.  The file is .xlsm when opening I get the enable editing which I say yes to.  Double checked and there is code on the modFloatCombo module and Sheet2(PrSchTab).  At a lost as to why the code is not working.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I downloaded the file I posted in post ID 40553830, clicked on cell A35, pressed the little dropdown arrow, chose "BA" and got just what the picture in post ID 40557835 shows, so if that's what you are doing and it doesn't work for you, I'm stumped.

We might be able to find out what's going on if we debug it together but if you want to do that I'd like to schedule an hour with you when we're both available so we can do it "live".
0
 

Author Comment

by:jmac001
Comment Utility
Hi Martin,  

The issue that I am having is related to being on network.  When I downloaded and opened off of network I was able to get the filters to work as demonstrated in your video.  Is this something that you would be able to troubleshoot?   Do I need to consider or is there a different way to get the information that I am looking for?

I noticed In the current code you can only update the schedule for 1 item on the first tab.  The end goal is for the user to have a date populated for each one of the categories (Tile, Perimeter, Floor, Store Front, etc).
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Unfortunately I have zero expertise when it comes to running Excel on a network so I think you'll need to ask for someone else's help. I'm not upset and I'm happy to know what the problem was, but in future questions you should make sure to mention that you want to run from the network if that's the case.
0
 

Author Closing Comment

by:jmac001
Comment Utility
Thanks for your patients while I troubleshoot the issue, this is the first time that VBA has not worked on the network.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
You're welcome and when you find out why, please keep me informed.

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 Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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 …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

772 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