Formula Cell Reference Limit (25,000,000) Not Refreshing

jordan.navarro23 · 05/10/23

Hi all,

We're having an issue where the 25,000,000 formula cell reference limit was reached after adding a new column, but even after deleting way more columns and cell references than we added, it's still showing the attached error message and not letting us move any new rows into the sheet.

We should be far below the limit at this point, but I can't find anything else to delete without risking a significant loss of functionality. Is there a way to check how many formula cell references we have vs. the 25m limit? Or could this be a Smartsheet server issue where something needs to be refreshed on the backend before it recognizes we're actually below the limit?

2023-05-10 13_57_21-Master TTT - VDO2 - Smartsheet.com.png

Genevieve P. · 05/17/23

Hiya! Happy to jump in here.

The total count of cells referenced by formulas in a sheet cannot exceed 25 million, as you've found. This includes cross-sheet references, in-sheet cell references, column references, and range references.

However, I believe that only cells that contain formulas are counted towards the limit. What I mean by this is that if cell [Primary]1 is static, and cell [Column 2]1 has a formula like =[Primary]1 , then referred cell count is 0. But if a formula is added to [Primary]1, that's when the referred cell count is 1.

Then if you have column references that are looking at an entire column (and that referenced column also has a formula), each individual cell is referencing the entire column of 2,824 cells (2,824formula cells x 2,824referenced cells = 7,974,976) as Paul noted.

This should help explain why your sheet wasn't hitting the limits before, but once you have overlapping formulas, the referenced count can shoot way up.

If you're having issues, you can reach out to Support for clarification about this one sheet, however they will likely advise you to reduce your formula count or simplify the formulas that are being used.

Cheers,

Genevieve

Genevieve P. · 05/18/23

Hi @jordan.navarro23

First scenario: a column formula looking at a column with 3,000 cells that are plain text. This would count as 0 towards your 25million reference limit (however it does add to the complexity of the sheet in general).

Second scenario: a single formula in one cell referencing a column with 3,000 cells, each of those containing a formula. This counts as 3,000 towards your 25million reference limit, as you're referencing 3,000 formulas in one reference.

Third scenario: a column formula in 3,000 cells where each instance is referencing an entire column of 3,000, each of those referenced cells containing a formula. This counts as 9,000,000 towards your 25million reference limit (3,000 formulas x 3,000 cells with formulas).

Keep in mind that each time a reference is used it adds to the total, meaning that if you have the same reference listed twice in one formula, both times count as a reference (as in my last column example on the right).

Note also that {cross sheet references} add counts as well, so if your formulas are looking into a sheet that houses formulas, these will be included in your formula reference count.

So in your original example:

=INDEX({VCA Contact List Range 4}, MATCH(Route@row, {VCA Contact List Range 2}, 0))

Route@row, {VCA Contact List Range 4}, and {VCA Contact List Range 2} will all count if they're housing formulas.

Does that make more sense?

Cheers,

Genevieve

Paul Newcome · 05/11/23

How many rows do you currently have in your sheet, and what is the new formula?

The only way to check is to manually do the math.

jordan.navarro23 · 05/12/23

@Paul Newcome The sheet has 2824 rows and the new column formula is as follows:

=IF([Equipment Type]@row = "Tablet", "Tablet", "Tester")

That would only add 2824 formula cell references to the report, is that right? If my math is correct on the attached spreadsheet, we should only have a total of 149,672 formula cell references on the sheet, but maybe I'm missing something.

Thanks for your help!

Formula Cell Reference Check.xlsx

Paul Newcome · 05/13/23

It looks like you also have some full column ranges. This means that in a single Column:Column reference, you are referencing 2,824 cells 2,824 times. That one reference actually takes up 7,974,976 of the available references. You do that 3 times and you are already at almost 24 million of the available 25 million.

jordan.navarro23 · 05/15/23

@Paul Newcome if that's the case, wouldn't the sheet have already reached capacity once we exceeded 3 full column ranges?

I was able to rearrange some things and get that error message to go away, so everything's working fine now, but I'm counting a total of 21 full column ranges included in various formulas, and the sheet has even more rows than it did before (2935 rows). Wouldn't that come out to something like 180+ million references, not including additional single-cell references?

Paul Newcome · 05/15/23

That's how it was explained to me by Support when I ran into that issue some years ago. The way things get counted may have changed since then. I will dig into it a bit more to see if I can find out if something has changed.

@Genevieve P. Would you happen to have any insight?

Genevieve P. · 05/17/23

Hiya! Happy to jump in here.

The total count of cells referenced by formulas in a sheet cannot exceed 25 million, as you've found. This includes cross-sheet references, in-sheet cell references, column references, and range references.

However, I believe that only cells that contain formulas are counted towards the limit. What I mean by this is that if cell [Primary]1 is static, and cell [Column 2]1 has a formula like =[Primary]1 , then referred cell count is 0. But if a formula is added to [Primary]1, that's when the referred cell count is 1.

Then if you have column references that are looking at an entire column (and that referenced column also has a formula), each individual cell is referencing the entire column of 2,824 cells (2,824formula cells x 2,824referenced cells = 7,974,976) as Paul noted.

This should help explain why your sheet wasn't hitting the limits before, but once you have overlapping formulas, the referenced count can shoot way up.

If you're having issues, you can reach out to Support for clarification about this one sheet, however they will likely advise you to reduce your formula count or simplify the formulas that are being used.

Cheers,

Genevieve

jordan.navarro23 · 05/17/23

@Genevieve P. Thanks for the info!

Just so I'm 100% clear, if I have a sheet containing 3,000 rows, and I have a column formula that refers to an entire column range on the same sheet, but that range doesn't contain any formulas, would that count as 3,000 formula cell references or 0?

Then, if I added a column formula in that range, would that count as 3,000 formula cell references or 9,000,000?

Genevieve P. · 05/18/23

Hi @jordan.navarro23

First scenario: a column formula looking at a column with 3,000 cells that are plain text. This would count as 0 towards your 25million reference limit (however it does add to the complexity of the sheet in general).

Second scenario: a single formula in one cell referencing a column with 3,000 cells, each of those containing a formula. This counts as 3,000 towards your 25million reference limit, as you're referencing 3,000 formulas in one reference.

Third scenario: a column formula in 3,000 cells where each instance is referencing an entire column of 3,000, each of those referenced cells containing a formula. This counts as 9,000,000 towards your 25million reference limit (3,000 formulas x 3,000 cells with formulas).

Keep in mind that each time a reference is used it adds to the total, meaning that if you have the same reference listed twice in one formula, both times count as a reference (as in my last column example on the right).

Note also that {cross sheet references} add counts as well, so if your formulas are looking into a sheet that houses formulas, these will be included in your formula reference count.

So in your original example:

=INDEX({VCA Contact List Range 4}, MATCH(Route@row, {VCA Contact List Range 2}, 0))

Route@row, {VCA Contact List Range 4}, and {VCA Contact List Range 2} will all count if they're housing formulas.

Does that make more sense?

Cheers,

Genevieve

jordan.navarro23 · 05/18/23

@Genevieve P. That makes total sense, thanks for clarifying!

Paul Newcome · 05/18/23

@Genevieve P. Yes! Thank you for clarifying. I wasn't aware that it only counted if the cell being referenced also had a formula in it.

Samuel Mueller · 03/25/24

@Genevieve P. just wondering if there is official documentation of this that we can reference? I looked but could not find.

Also, thank you so much for explaining this, it is very helpful.

Genevieve P. · 03/26/24

Hey @Samuel Mueller

I'm glad the explanation was useful! I don't believe there's a Help Article that details the limit other than the error message itself.

Formula Cell Reference Limit (25,000,000) Not Refreshing

Best Answers

Answers

Help Article Resources

Categories