I've created a report which orders and summarises jobs belonging to customers based on profit margin. ie customer with highest margin across all jobs per customer first, lowest margin last. It took me a while to figure out but I managed to get this working using the minimum of filters. However, now that I'm trying to add filters to make it more usable, I'm finding the formula that changes the order of the report is too long if it contains all the filters I need it to. I basically have the following formulas with which other simple formulas link.

Cell E22:

=NL("Rows=5","Customer","No.",\$M\$2,"*","Company=",\$D\$18)

Cell C23:

=NL("Rows","Contract",,"Contract No.",NL("Filter","Contract Ledger Entry","Contract No.","Posting Date",\$Z\$3,"Contract No.",\$Z\$4,"Company=",\$D\$18),"Global Dimension 1 Code",\$X\$3,"Global Dimension 2 Code",\$X\$4,"Shortcut Dimension 3 Code",\$X\$5,"Shortcut Dimension 4 Code",\$X\$6,"Shortcut Dimension 5 Code",\$X\$7,"Bill-to Customer","@@"&E22,"Company=",\$D\$18)

Cell M2 (The problem formula!)

="-=NL(""Sum"",""9066232"",""9066229"",""4"","""&\$G\$14&""",""2"",NL(""Filter"",""Contract"",""1"",""2"",""@@""&NF(,""No.""),""204"","""&\$G\$8&""",""205"","""&\$G\$9&""",""206"","""&\$G\$10&""",""207"","""&\$G\$11&""",""208"","""&\$G\$12&""",""Company="","""&\$G\$13&"""),""Company="","""&\$G\$13&""")"

The formula which is causing me problems is M2. I've replaced the field names with the field/table references to try and reduce the length of the formula but it's not short enough. If I take out 2 filters it works.

I've thought of using "filters=" but I can't get this to work. I suspect it might not work within double quotes.

Can anyone give me any suggestions/workarounds to get my formula in cell M2 working?

Thanks

#### 1 comment

• Harry Lewis

Hello Colin.

Yes, you can use "Filters=" within an NL(Filter) function that is embedded within an NL(Sum) function with, in turn, is embedded within an NL(Rows) function as part of a Sort-by-Sum.

The basic syntax is something like this:

=NL("Rows","Cust. Ledger Entry","Customer No.","-=NL(""Sum"",""Cust. Ledger Entry"",""Amount"",""Entry No."",NL(""Filter"",""Cust. Ledger Entry"",""Entry No."",""Customer No."",NF(,""Customer No.""),""Filters="",\$C\$3:\$D\$4))","*")

which, if using only table and field numbers, would look like this:

=NL("Rows","21","3","-=NL(""Sum"",""21"",""13"",""1"",NL(""Filter"",""21"",""1"",""3"",NF(,""3""),""Filters="",\$C\$3:\$D\$4))","*")