Consulting

Results 1 to 5 of 5

Thread: Filter formula include argument

  1. #1
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    109
    Location

    Filter formula include argument

    Greetings..

    I am struggling with applying the Filter formula in an Excel table. I had it working in another instance, but it doesn't seem to be consistent.

    Here is my formula...
    =FILTER(BkgTbl[[#All],[Group]:[Rtn Depart Time]],({1,0,1,1,1,0,0,0,0,0,0,0,1,1,1,1,1,0,0,0,0,0,1,0,0,0,0,1})*(BkgRegT="Yes"),"")
    BkgRegT is a named column range within BkgTbl. Removing the 2nd condition returns all the columns required properly. Adding the second condition returns a "#VALUE" error, and hovering over that says there is a data type problem. The table data in that column is either blank or "Yes".

    What have I done wrong?

    Thanks
    Gary

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,902
    I'm not at all clear on how many tables there are; is the 'applying the Filter formula in an Excel table' the same table as BkgTbl?
    Putting the Filter function within any table will probably have Excel complaining.
    Are you trying this:
    =FILTER(FILTER(BkgTbl[[#All],[Group]:[Rtn Depart Time]],({1,0,1,1,1,0,0,0,0,0,0,0,1,1,1,1,1,0,0,0,0,0,1,0,0,0,0,1})),BkgTbl[[#All],[BkgRegT]]="Yes","")
    if so, you'll get the same result with the shorter:
    =FILTER(FILTER(BkgTbl[[Group]:[Rtn Depart Time]],({1,0,1,1,1,0,0,0,0,0,0,0,1,1,1,1,1,0,0,0,0,0,1,0,0,0,0,1})),BkgTbl[BkgRegT]="Yes","")
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    109
    Location
    There is one table: "BkgTbl".
    [Group] is the header of col B and [Rtn Depart Time] is col AC.
    BkgRegT was the name of a named range within the same table.

    Yes, I had tried to separate the filters into two runs, but had the same errors. I don't know why, but I fiddled a bit with it again and found this to work...

    =FILTER(FILTER(BkgTbl[[Group]:[Rtn Depart Time]],({1,0,1,1,1,0,0,0,0,0,0,0,1,1,1,1,1,0,0,0,0,0,1,0,0,0,0,1})),BkgRegT="Yes","")
    Not sure on why... but it works. Yours didn't work, probably because the BkgRegT is a named column in the table and not a column header value. Yet, the second one is almost identical to the one that worked... just took out the Table name and turned the field into a named column.

    Thanks for your help.

    Gary

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,902
    Quote Originally Posted by garyj
    Not sure on why...
    BkgRegT and BkgTbl[[Group]:[Rtn Depart Time]] have the same number of rows.
    BkgRegT and BkgTbl[[#All],[Group]:[Rtn Depart Time]] do not have the same number of rows.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    109
    Location
    Quote Originally Posted by p45cal View Post
    BkgRegT and BkgTbl[[Group]:[Rtn Depart Time]] have the same number of rows.
    BkgRegT and BkgTbl[[#All],[Group]:[Rtn Depart Time]] do not have the same number of rows.

    Thanks... sometimes my mindblock is larger than my mindsolve.
    Gary

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •