Error when running out of CPU memory

markkrebs
markkrebs ✭✭✭✭✭✭
edited 01/07/22 in Smartsheet Basics

Not really a question but more of a heads up. I had a sheet with almost the max rows and 6 columns and 2 references. Tried to add a column formula and my CPU ran out of memory. After the browser crashed I went back to the sheet and smartsheet threw the exceeds 2.5 Million cell reference error. Im no where near 2.5 million. I escalated to support for this instance but I believe this is a platform issue where once the CPU runs out of memory the sheet gets corrupted and needs to be rebuilt. This has happened a few times in the last 6 months on different sheets (some no where near the cell limit). Hopefully engineering can diagnose and implement a fix.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The 2.5 million cell reference limit can actually be hit very quickly when you have a lot of rows. Think of it this way...


    You have 10 rows and 3 columns. The third column has a basic SUMIFS where you add up everything in the first column based on the entry in the second column.


    =SUMIFS([Column 1]:[Column 1], [Column 2]:[Column 2], @cell = [Column 2]@row)


    This one single formula already has 21 cell references in it. 10 for Column 1, 10 for Column 2, and then 1 for the single cell reference.


    Now you put this formula in all 10 rows. Now you have 10 formulas referencing 21 cells each so with only 10 rows and 3 columns (30 cells used total) you are referencing 210 cells.


    Lets say you use this same concept on 1000 rows. Now you have 1000 references for column 1, 1000 references for Column 2, and 1 reference for the single cell reference. That brings you to 2,001 in just one single formula. Fill that formula down into all 1000 rows and this one formula referencing two columns is pulling a total of 2,001,000. So right there is just over 2 million cells being referenced already.

  • This content has been removed.
  • markkrebs
    markkrebs ✭✭✭✭✭✭

    Thats not what caused this - its a Smartsheet limitation / cap