Unsure if this is actually in reality an Excel issue, but I just spent a couple of days troubleshooting a report that contained a formula that was simply too long. It appears there is a 255 character limit. Excel (2007) does not state it has a problem with this, but Jet comes up with a message that you cannot mix nested formulas and names or something (only after three days did i see it may be that it was just too long).
Now, which do you feel is more legible?
–
=NL("Rijen";"Vendor Ledger Entry";"Vendor No.";"Limiet=";"20";"+=NL(""Som"";""Vendor Ledger Entry"";""Amount"";""Vendor No."";NF(;""Vendor No."");""Document Type"";""Invoice|Credit Memo"";""Open"";""<>0"";""Due Date"";"""&$B$43&""")";"*";"Document Type";"Invoice|Credit Memo";"Open";"<>0";"Due Date";$B$43)
–
OR
–
=NL("Rijen";$L$47;$M$47;$L$48;$M$48;"+=NL(""Som"";"""&$L$47&""";""Amount"";"""&$M$47&""";NF(;"""&$M$47&""");"""&$L$49&""";"""&$M$49&""";"""&$L$50&""";"""&$M$50&""";"""&$L$51&""";"""&$M$51&""")";"<>0";$L$49;$M$49;$L$50;$M$50;$L$51;$M$51)
–
The latter is btw also the longest possible as adding an L/M52 pair (i.e. COMPANY=) makes it go over 255…
I vote for the first option, which would require a stretch of the maximum formula length to more than 255 chars. Who else feels this to be desirable?
Date
Votes
17 comments
-
Jet Reports Historic Posts I've yet to discover this limitation, but thanks for stating it!
it's also closely related to this one, which is a better known one:
http://community.jetreports.com/viewtopic.php?f=6&t=256 -
Jet Reports Historic Posts You are right, Chris. Excel 2003 has its limitations - and although the first option you mention is slightly less cryptic… if it doesn't fit it's not accepted by Excel.
I guess Jan you are using Excel 2007… that's a way to solve the problem ;)
Another option could be to use Nameboxes. Did you ever used that?
In Chris' example it could be like this: Cell L47 contains "Vendor ledger entry" and is called "VndrLdgr" in the Namebox. The function could then be =NL("Rows",VndrLdgr…
A bit more work, but less cryptic… hope this helps, Chris. -
Jet Reports Historic Posts …
I guess Jan you are using Excel 2007… that's a way to solve the problem ;)
…
What I understood from Chris was, although excel allows it, Jet still doesn't…
About the same as the limitations in columns and lines, excel 2007 allows more than 65k columns, but jet doesnt.
Chris, can you state your problem, because I think I missunderstood it? -
Jet Reports Historic Posts Hmmmm… interesting… I thought Chris was using Excel 2003 but now I see he mentions Excel 2007…
I copied Chris' formula into my Excel 2007… It runs perfect! No problems at all with the max. length. Actually, I never had issues with the max. characters after I installed Excel 2007.
You are right Jan about the 65K Rows limit that Jet still has in Excel 2007 :( I have heard several people making remarks about this… it's strange that Jet still hasn't solved this as other tools we know of don't have such a problem at all. -
Jet Reports Historic Posts Hmmm… Maybe yet another trick played on me by the 'kaboutertjes' over here…
However, to reproduce I can do the following:
- Start Excel 2007
- Copy the longer formula into cell D4
- Change B43 to D3 because that looks better ;)
- Enter 01-08-2008..31-08-2008 into D3
- Hit the Jet 'Report' button
–> Error message (translated): "The formula on sheet 'Sheet1' in address '$E$5' cannot be used in combination with NL("Rows"), NL("Columns") or NL("Sheets"). Possible causes include the formula being too long, or it containing both nested functions and named ranges (the two cannot be used simultaneously)."
For me, shortening the formula made the error disappear. -
Jet Reports Historic Posts Hmmm… Maybe yet another trick played on me by the 'kaboutertjes' over here…
However, to reproduce I can do the following:
- Start Excel 2007
- Copy the longer formula into cell D4
- Change B43 to D3 because that looks better ;)
- Enter 01-08-2008..31-08-2008 into D3
- Hit the Jet 'Report' button
–> Error message (translated): "The formula on sheet 'Sheet1' in address '$E$5' cannot be used in combination with NL("Rows"), NL("Columns") or NL("Sheets"). Possible causes include the formula being too long, or it containing both nested functions and named ranges (the two cannot be used simultaneously)."
For me, shortening the formula made the error disappear.
The 'named range error' could be because you've made an error with quotes. Check all your cell adresses if they are entered correctly…
Can you paste your new formula otherwise? -
Jet Reports Historic Posts Actually, I didn't change anything - I copy/pasted directly from this web page to make sure I didn't get a mix-up there… Judging by your reply I assume this does not produce an error in your configuration?
-
Jet Reports Historic Posts I've encountered this problem just yesterday and found out what it was. I've had much longer functions before but this one gives an error.
While it can still fit in excel, and you can have longer "jet functions", the problem is that one jet function is too long (for example an NL with some "NP filters" in).
While you are able to have multiple jet functions and exceed that limit by far (fe: GL+GL/NL), you're not able to create one over-sized jet function.
Regards,
Jan -
Jet Reports Historic Posts I am experiencing this same problem with Excel 2007. If I am reading what Jan wrote correctly you can nest functions together, as long as any of the individual functions do not exceed 255?
This works and is in total under the 255 limit;
=NL("Rows", NP("Difference",NL("Filter","Customer","No.","Salesperson Code","BILL|AGP-Bill"),NL("Filter","Sales Invoice Line","Sell-to Customer No.","Quantity","<>0","Amount","<>0","Salesperson Code","BILL|AGP-Bill","Posting Date","11/01/08..11/30/08")))
However when I change it to;
=NL("Rows", NP("Difference",NL("Filter","Customer","No.","Salesperson Code","BILL|AGP-Bill","Blocked","<>Ship"),NL("Filter","Sales Invoice Line","Sell-to Customer No.","Quantity","<>0","Amount","<>0","Salesperson Code","BILL|AGP-Bill","Posting Date","11/01/08..11/30/08")))
Pushing the total over 255 I get the error. I am guessing that it is considering my NL(Rows) to be over 255?
Is there some way around this or is Jet working to remove this hard limit? I specifically bought Excel 2007 to circumvent it, but unfortunately am exactly where I started, and I hate Excel 2007 (MS's Evil Ribbon Empire… :-( )
Any tricks, or even a way to re-write this to come in under the wire would be appreciated, ty. -
Jet Reports Historic Posts phope,
I would rewrite the formula
somthing likeC6 =NL("Filter","Customer","No.","Salesperson Code","BILL|AGP-Bill") D6 =NL("Filter","Sales Invoice Line","Sell-to Customer No.","Quantity","<>0","Amount","<>0","Salesperson Code","BILL|AGP-Bill","Posting Date","11/01/08..11/30/08") E6 =NL("Rows", NP("Difference",C6,D6))
Then you don't have any problems with formula length.
I don't know if this limit will be changed. I talked to Jet Reports support once about this issue, and they say that even though you are on Excel 2007, the routines that they use in VBA still have a formula limit of 255 characters. -
Jet Reports Historic Posts If the problem is formula length, you can replace table names with their ID number. I believe the same can be done with the field names (but I can't be sure). Not pretty but has worked for me from time to time.
-
Jet Reports Historic Posts Replying an rather old thread, but I have had the same problem with a checklist report where conditionally different Item fields were checked for their values. And little bit of a Value OR Value OR Value report:
NL("Rows";"Item";;"OR(NF(;""Name"")="""";ETC;ETC;ETC)";TRUE)
Changing all the Table and Field names to their respectable numbers (Item table: 27, field Name: 2) and the Option values to their Number variant can give you allot of space to work with.
I still end up in splitting the report in 2 where the first checks 1 set of fields and the other the other set. -
Jet Reports Historic Posts I am tagging onto this thread as it seems slightly relevant,
If using Excell 2007 is there still the C65,000 line limit when using Jet? If so I will not bother with 2007!!
This limitation is realy stoping some of my data extraction requirements, for example we have over 150,000 individual Stock lines and at times I may want to extract the whole list to compare with external data, if the comparison filter is not available as a field in the products table I have no-way of filtering whilst designing the report??
Any ideas? -
Jet Reports Historic Posts Following formula gives me message: No room to insert rows. (See attachment)
=NL("Rows";"Integer";"Number";"Number";"1..80000")
This is in O2K7 with JR7 and NAV2K9 in normal XLSX file. -
Jet Reports Historic Posts Sebastiaan,
Thanks for the reply and helpful screen shot, I will stop presurising my I.T department to upgrade!
Does anyone know if Jet will be lifting/correcting this restriction in line with Excel 2007?
Cheers,
Dean -
Jet Reports Historic Posts The formula length and row / column limits are supposed to be removed in Jet Reports 2009. I'm eagerly waiting for the beta to be able to test.
-
Jet Reports Historic Posts Chuck,
Thanks for the update, If you find out one way or the other can you let me know??
Cheers
Dean
:)
Please sign in to leave a comment.