Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Add new fileds to filter layers Excel

Posted on 2012-04-08
13
Medium Priority
?
417 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
  • 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 2000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

916 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