Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

Add new fileds to filter layers Excel

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
Rayne
Asked:
Rayne
  • 10
  • 3
1 Solution
 
dlmilleCommented:
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
 
RayneAuthor Commented:
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
 
dlmilleCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
RayneAuthor Commented:
Now that makes it crystal clear :) Now I understand. Thank you Dave for the explanation
0
 
RayneAuthor Commented:
Awesome expert help as always..
0
 
dlmilleCommented:
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
 
RayneAuthor Commented:
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
 
RayneAuthor Commented:
I am PRO and seasoned filter check-box creator now :)
0
 
RayneAuthor Commented:
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
 
RayneAuthor Commented:
0
 
RayneAuthor Commented:
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
 
RayneAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 10
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now