0

Error Excel memory limitations

Jet Reports Historic Posts

January 24, 2017 21:35

None

Follow

Hi,

I get the following error when I run a report

"Due to Excel memory limitations, the formula could not be retrieved from range $1:$14569."

I am running Jet Professional. The report was working previously and now it says this.

Any ideas?

Thanks

• Facebook
• Twitter
• LinkedIn

5 comments

• 0
  
  Jet Reports Historic Posts January 25, 2017 15:24

  14,569 rows can be fairly substantial (especially if there are many column involved, as well).
  
  Sometimes Excel will "remember" the number of rows and columns that were *ever* present on a worksheet - even when that worksheet no longer has all that data [as can be the case when running a report that includes replicating functions - NL(Rows) or NL(Columns) ]
  
  Here's something you can try:
  
  IMPORTANT Prep: Save a backup of your report (you're about to make changes to it)
  
  1) With the report in DESIGN mode, press CTRL-END
  
  2) Note the cell to which this takes you (in the example above: CE106424)
  3) If this cell is substantially beyond (row and/or column) of where all your functions/etc are…
  4) Delete the extra rows and columns by selecting the entire rows, right-click, and choose DELETE and then doing the same for all the columns.
  
  5) IMPORTANT!!! Save your workbook, Close the workbook, re-open the workbook
  6) With the report still in DESIGN mode, press CTRL-END
  7) This *should* take you to a much more reasonable cell.
  8) Try running your report
• 0
  
  Jet Reports Historic Posts January 29, 2017 22:19

  I think there is something to what you are saying but:
  
  When in design mode CTRL-END just takes me to a reasonable spot. When looking at the report and pressing CTRL-END it takes me to row 14572 which is correct but to Column XFD. Deleting in this mode doesnt seem to do anything.
  
  Any other ideas?
  
  Thankyou for the help.

• 0
  
  Jet Reports Historic Posts January 30, 2017 19:42

  If there is actual data in all the columns up to XFD then the answer is that you've successfully created a report that returns more data than Excel can handle.
  
  If, however, this is not the case, there could be some issues for which you would probably want to contact Jet Reports Technical Support.
  
  For example, when a Jet report is run, copies of functions and formulas are created so that the report can be put back in Design mode - at which time, these backup copies are then removed. If something interferes with that removal process, former backup copies could interfere with the running process. Tech Support would be able to tell. (some of us on this board could, too, but we don't want you to share your company's data in a public forum).

• 0
  
  Brett Post March 14, 2018 15:36

  Holy cow, that actually worked! In design mode, ctrl-end and deleting extra rows and column! Thank you so much! The one question I do have is why all of the sudden did I get that error when I had been using that report for years? I was getting the same error on ALL of my reports. So far deleting the extra rows and columns has worked to rid that error on just one of my reports. I will try the others

• 0
  
  Harry Lewis March 14, 2018 22:10

  Hi Brett -

  It's one thing to fix an issue.  It's very much another thing to tell you exactly what caused specific instances of an issue.

  I'm glad the steps worked for you.

  Edited by Harry Lewis March 14, 2018 22:10