100,000 cell reference limit - do I understand it right and can I monitor it?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/397095#Comment_397095

It's not ideal, but it has worked for our situation. (I do kind of get a thrill every time I figure out how to make Smartsheet do something it wasn't built to do, or skirt around a limitation like this!)

Seems like it won't be necessary come sometime in the next 6-9 months, which would be great because it will greatly speed up the lookup sheet.

Francisco

@Jeff Reisman - sure thing, it does help me to feel clever, but often we're looking for solutions replicable for less technical folks—moderate lift to address these limitations that impact QOL. We spent a few hours last night restructuring a project to have a structure similar to what you've mentioned.

Not that I don't love burning the midnight oil... We're pushing for SmartSheet to reduce the minutia of work - not introduce "unneeded" complexity.

Marlana K.

I am going to add a comment here as we are experiencing an issue with our indexing not "completing" . We have several reference sheets that are generated though an API between Smartsheet and our database. Looking at the Pharmacy Site Information (API) sheet activity log it completes successfully. When we look at various sheets it randomly stops and doesn't complete the update. Any ideas? They do not appear to be exceeding the limit as far as I can tell. Unfortunately we are not in our sheets at 2 AM PT when the API clears the Pharmacy Site Information (API) Sheet and repopulates it. Each sheet normally updates the indexing once that completes.

Gita Mooney

i am told that the limit is 5,000,000 now but I still got the error message the i had too many cells referenced by formulas. The irony is that i am not connected to nor pulling in values from any other sheet. It only has 3 if statements and one "Total"ing formula that adds up 16 columns in the SAME Sheet. How could I have hit the 5,000,000 limit? I have 3606 rows. Formulas 1,2,3 are IF statements, if value @row in Cell = "this, then "that" etc.

The last one is sum (Jan2024: April 2025) - 16 columns - ALL of these are for and IN the same sheet. Please help!

Stefan

Hi @Gita Mooney,

as you don’t use any cross sheet references or cell links, this is odd. Sounds like a case for Smartsheet support.
Did you contact them?

Hope they can help you and would be interested what the resolution will be.

Greetings

Stefan

Paul Newcome

@Gita Mooney and @Stefan There is also an in sheet limit that can be reached.

@Genevieve P. I remember there was a thread regarding this where you explained how to calculate this, but I can't seem to find it now.

Gita Mooney

@Stefan I have opened a support ticket but was curious to see if you guys had any ideas.

Genevieve P.

Hiya! Thanks for the tag @Paul Newcome, happy to help clarify.

The total count of cells referenced by formulas in a sheet cannot exceed 25 million. This includes cross-sheet references, in-sheet cell references, column references, and range references. However, only cells that contain formulas are counted towards the limit.

Here's an example of how to calculate the total referred cells:

• If a single formula refers to 10 columns containing formulas on a sheet with 10,000 rows, then that one formula is referencing 100,000 cells (10 columns x 10,000 rows = 100,000 cells).
• If that formula exists in 100 different cells, then the total referred cells is 10,000,000 (100,000 referred cells * 100 formulas = 10 million)

As another example, the 25,000,000 limit can be reached with only two columns in a sheet that has 5,000 rows:

• Column 1 has a column formula populating all 5,000 rows with the value "Hello"
• Column 2 also has a column formula, in this example: =COUNTIF([Column1]:[Column1], "Hello")

Adding this formula in Column 2 will result in the error being produced, because all 5,000 rows in Column 2 are referencing all 5,000 rows in Column 1, which is populated via a formula. 5,000 x 5,000 = 25,000,000, resulting in the limit being reached.

I hope this helps!
Genevieve

Gita Mooney

@Genevieve - I am not sure i understand. I have a Smartsheet that has 3600 rows and 100 columns. Of the 100 columns only 4 have formulas. 3 of the columns have formulas checking just one other column(Service)

Need for FLS - IF(Service@row = "PM Generator", "PMG_Gen", IF(Service@row = "PM FLS Maintenance", "PM_FLSMaint", IF(Service@row = "Weekly Diesel Pump Churn", "PM_WklyDiesel", IF(Service@row = "Monthly Electric Pump Churn", "PM_MthlElec", IF(Service@row = "PM Pumps (Fire Pump Engine)", "PM_Pumps", "")))))

the Fourth column is an addition column which adds up each row for 16 columns - like

SUM([Jan 2024]@row:[Apr 2025]@row)

How would I calculate the references for those two types?

Genevieve P.

Hi @Gita Mooney

Does your "Service" column have a formula in it? Are any of your 3 formulas referencing columns that contain a formula?

Gita Mooney

@Genevieve Nope. The service column only has values and the 16 month columns just have numbers.

Gita Mooney

going by your example just the one formula i have which adds up 16 columns of 3600 rows each. Just that will put me over 25,000,000 right?

Genevieve P.

Hi @Gita Mooney

If you have 1 formula in 1 cell that only references one column with a column formula, then it's:

1 (single cell formula) x 1 (column) x 3,600 (cells with formulas) = 3,600

If 3 of your columns have column formulas, then:

1 x 3 x 3,600 = 10,800

1 (single cell formula) x 3 (columns) x 3,600 (cells with formulas in each column) = 10,800

However, in your case it's not a single-cell formula but another column formula:

3,600 x 3 x 3,600 = 38,880,000

3,600 (column formula with SUM(row) in each cell) x 3 (columns) x 3,600 (cells with formulas in each column) = 38,880,000

Does that explain your specific scenario?

Gita Mooney

@Genevieve P.

Is there any way I could send you a teams invite or a zoom invite and I can show you. Still not able to wrap my head around it.

I have 4 columns with formulas. 3 of them are of the type - check this one column, if the value is this, then this, else if this, then this, etc.

the fourth one adds up 15 columns for each row.

Genevieve P.

Hi @Gita Mooney

If you have a ticket open with Support this will be the better channel to share your sheet in a screen recording or video call.

The 4 individual columns with formulas won't be a problem at all, as long as they're referencing static cells. The referred cell limit is only if a formula is looking at a cell that contains a formula.

So:

I have 4 columns with formulas. 3 of them are of the type - check this one column, if the value is this, then this, else if this, then this, etc.

If the "check this one column" is just a regular column with manual data, this is 0 for referred cells. 🙂

The count only happens if you have a formula looking at another formula.

Your fourth formula, the one that adds up 15 columns is potentially where the issue lies. If just one of those columns (one of the 15) has a formula all the way down, that's now:

3,600 (formula to SUM each row in 3,600 cells)
x 1 (column with formula - other 14 don't count)
x 3,600 (cells with formulas in that column)
= 12,960,000

If two of the 15 columns contain a column formula:

3,600 (formula to SUM each row in 3,600 cells)
x 2 (column with formula - other 13 don't count)
x 3,600 (cells with formulas in that column)
= 25,920,000

The item to check is: do any of the columns between the range of [Jan 2024] to [Apr 2025] contain a column formula?

If so, do they need to have a formula? Or can you move that column out of the SUM range?

If none of this has helped, please do continue to follow-up with Support so you can show them your specific sheet and all the formulas being used and referenced.

Cheers,
Genevieve