Solved

Add new fileds to filter layers Excel

Posted on 2012-04-08
13
412 Views
Last Modified: 2012-06-20
Hi Dave,

I am following up on the previous question.

The steps to add a new filter item
1.      Add it to the range type
2.      Ensure to add it to the column in the main table
3.      Update VBA like adding a new line to it
I tried doing all this to add [P4] and [ExtraLarge] filters to the list but it won’t filter. If you could let me know what changes I need to make to add new items to the filter lists that would awesome. As more new filter will be added so. I have added the file that I worked on.

Regards
R
CopyInfo-r4-new-filters.xlsm
0
Comment
Question by:Rayne
[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
  • 10
  • 3
13 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37822228
You were very close - The VBA was spot on!  However, there are a couple required steps in the workbook.

1.  First, make the checkbox controls NAMED properly.  So ELarge needs to be named CB_ELarge and P4 needs to be named CB_P4 (as opposed to Check Box 11 or other number when they are originally created).
2.  On the Source sheet, the criteria TRUTH formulas on row 2 need to be expanded to SUM 4 rows, now that both criteria have 4 possible outcomes.  Rather than just change to ,1,4, I added an additional formula so this second step should never be required:

E.g.,

=COUNTA(OFFSET(P123_Range,,,,1))

Returns the number of rows in the P123_Range.  I made the change to check for # rows in each of the 3 existing criteria on Source tab, range I2:K2

See attached.  Try to make these changes on your original and see if it all comes up good ;)

Dave
CopyInfo-r4-new-filters-r1.xlsm
0
 

Author Comment

by:Rayne
ID: 37824834
Hi Dave,

Sorry for the delayed reply. I took some time to understand it closely. Now the filter things has become clear except...

For =IF(OR(VLOOKUP(K6,P123_Range,2,0)>0,SUM(OFFSET(P123_Range,0,1,3,1))=0),TRUE,FALSE)
Let me interpret this and see if I can get it
Condition1 = (VLOOKUP(K6,P123_Range,2,0)>0
Condition2 = SUM(OFFSET(P123_Range,0,1,3,1))=0)

If either of the condition1 or condition2 is satisfied, return TRUE, else FALSE.

When I click on a checkbox, condition1 is satisfied, and function registers a TRUE
I am not sure about condition2 – let say I click two checkboxes of the same filter level, then this condition will not sum to  0, rather sum to 2 correct?

Other than this, I think I got it :)

R
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37824850
What Condition 2 does is return TRUE if none of the checkboxes in that category are checked.  So if you check 1, 2, 3, or 4 of the items, the first one kicks in, but if none are checked, the second one (condition) kicks in - not checking any is as if checking all.  

Make sense?

Dave
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:Rayne
ID: 37824881
Now that makes it crystal clear :) Now I understand. Thank you Dave for the explanation
0
 

Author Closing Comment

by:Rayne
ID: 37824888
Awesome expert help as always..
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37824892
Just be sure that parameter in the SUM(OFFSET includes the # rows in your range, either do it manually or with the revised function I gave you (see underlined, below):

 =IF(OR(VLOOKUP(K6,P123_Range,2,0)>0,SUM(OFFSET(P123_Range,0,1,3,1))=0),TRUE,FALSE)

Dave
0
 

Author Comment

by:Rayne
ID: 37824922
Oh yeah, the culprit formula that got me confused. The moving range is definitely very needed and reduces the manual error risks greatly. Thank you Dave
0
 

Author Comment

by:Rayne
ID: 37824960
I am PRO and seasoned filter check-box creator now :)
0
 

Author Comment

by:Rayne
ID: 37874951
Hi Dave,

I have a question - is it possible to remove any content from main display page if all the controls are unchecked? I will post a question

Thank you
0
 

Author Comment

by:Rayne
ID: 37908803
0
 

Author Comment

by:Rayne
ID: 38055485
Hello Dave,
There is a question when you are free. Thank you
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_27746714.html
Rayne
0
 

Author Comment

by:Rayne
ID: 38107666
Hello Dave,

Here is recent follow up to your question:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_27764572.html

Thank you :)
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

624 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