Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Filters for Calculator

Posted on 2015-01-16
16
Medium Priority
?
73 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
[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
  • 9
  • 7
16 Comments
 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 40553830
Try this.
Q-28598152.xlsm
0
 

Author Comment

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

Expert Comment

by:Martin Liss
ID: 40553887
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

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

Expert Comment

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

Expert Comment

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

Expert Comment

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

Author Comment

by:jmac001
ID: 40560814
I am working to see if I can identify why the code is not working
0
 
LVL 49

Expert Comment

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

Expert Comment

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

Expert Comment

by:Martin Liss
ID: 40563377
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
ID: 40564299
Thanks for your patients while I troubleshoot the issue, this is the first time that VBA has not worked on the network.
0
 
LVL 49

Expert Comment

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

722 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