0

Formula too long - can't add all the filters I require

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

Please sign in to leave a comment.