Solved

Add new fileds to filter layers Excel

Posted on 2012-04-08
13
406 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 41

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 41

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
 

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 41

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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: 37874961
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

920 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

14 Experts available now in Live!

Get 1:1 Help Now