Filter formula include argument

[garyj]

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

[p45cal]

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","")

[garyj]

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

[p45cal]

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.

[garyj]

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. :yes
Gary