Solved

Add new fileds to filter layers Excel

Posted on 2012-04-08
13
405 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel 2013 Problem 12 47
Connect to a database from Excel using JDBC instead of ODBC 3 26
Excel 3 22
MS Office subscription 11 33
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

705 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

17 Experts available now in Live!

Get 1:1 Help Now