超过细胞Reference of 25M

Julie Fortney

Hello Community friends!

I and a customer of mine have both had complex sheets suddenly start throwing this error message:

Some cross-sheet formulas can't be updated, because this sheet has more cells referenced from other sheets than the maximum allowed (100,000).

I contacted Smartsheet support about my sheet, and they stated that I am actually exceeding the 25M cell reference limit. Now, the difference between last week, when I didn't see this error, and yesterday, when the error popped up, is maybe two additional rows. My sheet contained about 20 columns with index/match formulas, and I realized that I could delete 5 of them that were not necessary. I figured that if I reduced the number of cell referencesper rowby 25% on a sheet with 150 rows, that would easily take us back under the 25M limit.

Unfortunately not - I am still getting the error.

I am in touch with Smartsheet Support about this, but I wanted to reach out to others in the Community to see if you have suddenly started experiencing this in the past two weeks. Our customer is also working with Support, because their sheet is similar to mine - their number of rows stays fairly consistent and the sheet was fine before last week.

Thanks for any insight!

Leibel S

@Julie Fortney

Read through this to get a bit of an understanding about the 25M issue

Just wanted to take a minute to share a quick analysis regarding the "Too many cells referenced" error. Smartsheet limits your cell references to 25M within a single sheet. Depending on the size of your sheet, that is either plenty of references or not nearly enough. <\/p>

The Crux of the Issue<\/strong><\/p>

The main issue is the use of column formulas that reference other columns. Consider this situation, where the formula column ("New") checks if boys names popular in 2021 ("2021") have been popular for the last decade ("Decade"):<\/p>

=IF(CONTAINS([2021]@row, Decade:Decade), "No", "Yes")<\/p>

https:\/\/us.v-cdn.net\/6031209\/uploads\/Y6448TBQTO9X\/image.png<\/a><\/p>

This table is made of 10 rows, so the column formula is generated 10 times, one for each row. You have two reference types in this column formula; an @row reference ("[2021]@row") and a column reference ("Decade:Decade"). <\/p>

• In the column formula, "[2021]@row" generates 10 cell references, because each <\/em>of the 10 formulas refers to the single <\/em>2021 entry on that row (10 formulas x 1 entry = 10 references).<\/li>
• In contrast, the "Decade:Decade" portion of the formula generates 100 reference, because each <\/em>of the 10 formulas refer to each <\/em>of the 10 entries in the Decade column (10 formulas x 10 entries = 100 references). <\/li><\/ul>

  Takeaway: @row cell refences grow linearly--use as many as you want. column:column references grow exponentially -- <\/em>these are what will restrict your page. Again, as your number of rows increase, the number of cells referenced grows exponentially.<\/p>

  Below is a chart that breaks this down by sheets with a given number of rows. <\/p>

  • "Rows": rows in a sheet<\/li>
  • "Cell References": how many cells a single<\/em> column:column refence will reference in a column formula.<\/li>
  • "# of Refs Allowed": how many column:column references you can make on a sheet with that number of rows <\/li><\/ul>

    https:\/\/us.v-cdn.net\/6031209\/uploads\/EOTKLMTCBHZJ\/image.png<\/a><\/p>

    As you can see, at 100 rows you can use as many column:column references as you please. However, once you get to 5,000 rows, you essentially can't use<\/em> column:column references in a column formula. A single column:column reference will blow through your entire allotment of 25M cell references and Smartsheet will not allow you to save until you fix this. Because of this restriction, for sheets that require heavy formula usage, I archive my data so that I keep under about 300 rows in a given sheet.<\/p>","bodyRaw":"[{\"insert\":\"Just wanted to take a minute to share a quick analysis regarding the \\\"Too many cells referenced\\\" error. Smartsheet limits your cell references to 25M within a single sheet. Depending on the size of your sheet, that is either plenty of references or not nearly enough. \\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"The Crux of the Issue\"},{\"insert\":\"\\nThe main issue is the use of column formulas that reference other columns. Consider this situation, where the formula column (\\\"New\\\") checks if boys names popular in 2021 (\\\"2021\\\") have been popular for the last decade (\\\"Decade\\\"):\\n=IF(CONTAINS([2021]@row, Decade:Decade), \\\"No\\\", \\\"Yes\\\")\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/Y6448TBQTO9X\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/Y6448TBQTO9X\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"This table is made of 10 rows, so the column formula is generated 10 times, one for each row. You have two reference types in this column formula; an @row reference (\\\"[2021]@row\\\") and a column reference (\\\"Decade:Decade\\\"). \\nIn the column formula, \\\"[2021]@row\\\" generates 10 cell references, because \"},{\"attributes\":{\"italic\":true},\"insert\":\"each \"},{\"insert\":\"of the 10 formulas refers to the \"},{\"attributes\":{\"italic\":true},\"insert\":\"single \"},{\"insert\":\"2021 entry on that row (10 formulas x 1 entry = 10 references).\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"In contrast, the \\\"Decade:Decade\\\" portion of the formula generates 100 reference, because \"},{\"attributes\":{\"italic\":true},\"insert\":\"each \"},{\"insert\":\"of the 10 formulas refer to \"},{\"attributes\":{\"italic\":true},\"insert\":\"each \"},{\"insert\":\"of the 10 entries in the Decade column (10 formulas x 10 entries = 100 references). \"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"Takeaway: @row cell refences grow linearly--use as many as you want. column:column references grow \"},{\"attributes\":{\"italic\":true},\"insert\":\"exponentially -- \"},{\"insert\":\"these are what will restrict your page. Again, as your number of rows increase, the number of cells referenced grows exponentially.\\nBelow is a chart that breaks this down by sheets with a given number of rows. \\n\\\"Rows\\\": rows in a sheet\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"\\\"Cell References\\\": how many cells a \"},{\"attributes\":{\"italic\":true},\"insert\":\"single\"},{\"insert\":\" column:column refence will reference in a column formula.\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"\\\"# of Refs Allowed\\\": how many column:column references you can make on a sheet with that number of rows \"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/EOTKLMTCBHZJ\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/EOTKLMTCBHZJ\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"As you can see, at 100 rows you can use as many column:column references as you please. However, once you get to 5,000 rows, you essentially \"},{\"attributes\":{\"italic\":true},\"insert\":\"can't use\"},{\"insert\":\" column:column references in a column formula. A single column:column reference will blow through your entire allotment of 25M cell references and Smartsheet will not allow you to save until you fix this. Because of this restriction, for sheets that require heavy formula usage, I archive my data so that I keep under about 300 rows in a given sheet. \\n\\n\"}]","format":"rich","dateInserted":"2022-01-13T12:58:02+00:00","insertUser":{"userID":129958,"name":"Lucas Rayala","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Lucas%20Rayala","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3C9S_9DsC3w!XMDTX-y-BOY!7HVumwhOGBb","dateLastActive":"2023-08-17T16:10:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/86492\/explanation-of-too-many-cells-referenced-error-25m-restriction","embedType":"quote","name":"Explanation of \"Too Many Cells Referenced\" Error - 25M Restriction"}"> https://community.smartsheet.com/discussion/86492/explanation-of-too-many-cells-referenced-error-25m-restriction

If you have a bunch of column formulas referencing entire columns in the sheet, and they are in turn being referenced by other columns, you can run into this issue pretty quickly

Julie Fortney

Hi@Leibel S,

Thanks for the info. I understand the limit; I'm just curious if there is a trend of others experiencing issues with sheets that weren't previously being alerted as exceeding the limit.