The formula below works fine when Agreement code (Dimension 8) is a long list of Agreement codes (48100,69200,74200,77200, etc) and also work s for one or two <>s (<>@@&<>5600. But the formula returns "Invalid filter" if an additional &<> value (<>@@&<>5600&<>7400) is added. Is there a way to filter on Dimension 8 with multiple <>s?
=IFERROR(-NL("Sum","Version Line Detail",BI$7,"0",BI$4,"Agreement Code",$E75,"Date",BI$8,"Fund No.",BI$10,"Budgeting Code",BI$13,"G/L Account No.",$I75),0)
I would try changing up the order of your filters and put the <>@@ as the last one. Perhaps Jet is reading it as "allow special characters " so instead of <>@@&<>5600&<>7400 you would have <>5600&<>7400&<>@@
Thanks Bryan. Changing the order of the filter has worked fine. With <>@@ at the beginning of the filter, I received the Invalid Filter error when I exceeded two <>S (<>@@&<>5600 works, but <>@@&<>5600<>7400 does not). By changing the order and putting <>@@ at the end of the filter, the formula returns values with multiple <>s as shown in my adjusted formula. =IFERROR(-NL("Sum","Version Line Detail","Amount","0","CP-Corporate","Agreement Code","<>5600&<>7400&<>48100&<>69200&<>@@","Date","10/01/2019..09/30/2020","Fund No.",100,"Budgeting Code","*","G/L Account No.","10000..98999"),0)
Thanks for your help!
Great to hear Bob! The only other suggestion I would make is to figure out why you need the IFERROR and figure out if you can remove it. Wrapping a Jet function in an Excel function can slow your report down. Sometimes you have to do it, but if you can avoid it, it can make a difference in large reports.
Thanks for the advice Bryan. I'll dig into why I included the IFERROR.
