Formulas referencing too many cells on source or destination sheet 👎

Mike Meyer · 09/01/23

"Formulas are referencing too many cells on the source or destination sheet. To run this workflow, please reduce the amount of cells referenced by formulas." How do I figure out how many and what formulas are connected to the sheet causing this error?

Neither the source nor destination sheets have the blue or black ▸in any cell denoting a referencing formula.

Paul Newcome · 09/01/23

That limit isn't strictly relegated to cross sheet references (blue or black arrows). If I have a column formula referencing a column in the same sheet [Column Name]:[Column Name], that counts towards that limit as well.

@Genevieve P. I remember we recently had a discussion on another thread where you were able to provide some really good insight into the cells being referenced limitations, but I can't seem to find it again. Would you happen to have a link handy?

Genevieve P. · 09/01/23

Hey! Thanks for the tag, @Paul Newcome

Was it this thread?

Genevieve P.

May 17, 2023

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

Or maybe this one?

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

Rob Wright

Apr 23, 2021

I have recently come across the limitation of smartsheet whereby you can only have 100,000 cell references, when I tried to add a vlookup where the table array was 140 columns wide, to a sheet that was already using several vlookus.
My first question is does the 100,000 cell limit mean that if for example I had 100 links to cells in other sheets, and two lookups that both referred to arrays in other sheets that were 200 rows deep and 50 columns wide, that I would have used up 100 + (200x50) + (200x50) = 20,100 cells references, and I would have 79,900 left to use in that particular sheet?
My second question is whether or not there is a metric anywhere which tells me how any cell references I have used, so that I know whether or not my next vlookup will work or not?
Any help appreciated.

Paul Newcome · 09/01/23

That limit isn't strictly relegated to cross sheet references (blue or black arrows). If I have a column formula referencing a column in the same sheet [Column Name]:[Column Name], that counts towards that limit as well.

@Genevieve P. I remember we recently had a discussion on another thread where you were able to provide some really good insight into the cells being referenced limitations, but I can't seem to find it again. Would you happen to have a link handy?

Mike Meyer · 09/01/23

Workflow:

Intake sheet: Data Shuttle destination for daily data import. Columns: 35; never more than 1000 rows shuttled per day. Automation-MOVE ROWS: every time a row is added triggers the move of each row to another destination sheet.

Destination sheet: This sheet receives (adding) each row from the Intake sheet. This is just a sheet to house all invoices for the current year. Columns: 44; the 9 extra columns are simple, non-cross sheet formulas like: =YEAR([Column A]@row, and to check a box in the "YTD" Column =IFERROR(IF([System Year {H}]@row = YEAR([Today {H}]@row), 1, 0), 0)); Current row count: 3527; Current cell count: 155,188

Reports: The destination sheet above is used as one of several source sheets in 3 Reports ("YTD", "PREV", and "LTD"). The 3 report use the same source sheets (sheet 1=2010, sheet 2=2011, sheet 3=2012..... through 2030).

Pivots: Many pivots are created using these reports. These Pivot configurations are set to update daily.

Rollup Sheets: Several rollup sheets are created and use cross-sheet formulas with the cells within the Pivots as references. I need this because as the year(s) go on, new elements (i.e. Jobs, Locations, PO's, etc.) populate the source sheets. And Pivot data doesn't look great on dashboards because they do not maintain the sorting selection chosen as new data is included.

I don't know if all of this info is info effects my problem, but wanted to be as specific as possible.

Thank you!

Genevieve P. · 09/01/23

Hey! Thanks for the tag, @Paul Newcome

Was it this thread?

Genevieve P.

May 17, 2023

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

Or maybe this one?

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

Rob Wright

Apr 23, 2021

I have recently come across the limitation of smartsheet whereby you can only have 100,000 cell references, when I tried to add a vlookup where the table array was 140 columns wide, to a sheet that was already using several vlookus.
My first question is does the 100,000 cell limit mean that if for example I had 100 links to cells in other sheets, and two lookups that both referred to arrays in other sheets that were 200 rows deep and 50 columns wide, that I would have used up 100 + (200x50) + (200x50) = 20,100 cells references, and I would have 79,900 left to use in that particular sheet?
My second question is whether or not there is a metric anywhere which tells me how any cell references I have used, so that I know whether or not my next vlookup will work or not?
Any help appreciated.

Mike Meyer · 09/01/23

@Paul Newcome @Genevieve P. You guys are the BEST! I'll check out that link and respond back. Paul, your responses here in the Community have helped me overcome at least 50 different obstacles/challenges! Thank you very much for your expertise and time!

Mike Meyer · 09/01/23

@Paul Newcome @Genevieve P.

Just for confirmation sake, can you please let me know if I'm calculating the cell references in just this ONE column formula in my sheet?

Current Row Count: 3,527

Column Formula:

=IF([Loc {H} # Only]@row <> "", IF([Contract Seq #]@row < 10, "R" + [Loc {H} # Only]@row + [Contract #]@row + "0" + [Contract Seq #]@row, IF([Contract Seq #]@row >= 10, "R" + [Loc {H} # Only]@row + [Contract #]@row + [Contract Seq #]@row, "")))

Calculation:

The above formula produces the correct Invoice # using the components [Loc # {H} Only] + [Contract #] + [Contract Seq #]

1. =IF([Loc {H} # Only]@row <> "",

Referencing a column to check a value: 1 x 3,527 = 3,527

2. IF([Contract Seq #]@row < 10,

Referencing a column to check a value: 1 x 3,527 = 3,527

3. "R" + [Loc {H} # Only]@row + [Contract #]@row + "0" + [Contract Seq #]@row,

Referencing a column to use the contents of the relevant cell as a component in a text (name) result of the formula: 3 x 3,527 = 10,581

4. IF([Contract Seq #]@row >= 10,

Referencing a column to check a value: 1 x 3,527 = 3,527

5. "R" + [Loc {H} # Only]@row + [Contract #]@row + [Contract Seq #]@row, "")))

Referencing a column to use the contents of the relevant cell as a component in a text (name) result of the formula: 3 x 3,527 = 10,581

Total:

= 3,527 + 3,527 + 10,581 + 3,527 + 10,581 = 31,743 cell references produced by this one column formula?

Mike Meyer · 09/01/23

⚡Update: @Paul Newcome @Genevieve P.

I changed the formula so all referenced cells/columns in the formula do not contain formulas themselves.

One of the original referenced columns "Loc # {H} Only" is a helper column and therefore DOES contain a column formula. So, I altered the formula by using another reference that does NOT contain a formula itself.

Is this the correct calculation? 🤷‍♂️

____

Contributing factors:

The below column formula produces the Invoice # using the components from columns: "Mac Loc" + "Contract #" + "Contract Seq #" *None of the these columns contain a formula.
Current Row Count: 3,527

Column Formula:

=IF([Contract Seq #]@row < 10, "R" + RIGHT([Mac Loc]@row, 2) + [Contract #]@row + "0" + [Contract Seq #]@row, IF([Contract Seq #]@row >= 10, "R" + RIGHT([Mac Loc]@row, 2) + [Contract #]@row + [Contract Seq #]@row, ""))

Calculation:

1. IF([Contract Seq #]@row < 10,

The referenced column "Contract Seq #" does NOT contain a formula.
So, count of cell references = 0?

2. "R" + RIGHT([Mac Loc]@row, 2) + [Contract #]@row + "0" + [Contract Seq #]@row,

The referenced columns "Mac Loc", "Contract #", "Contract Seq #" do NOT contain formulas.
So, count of cell references = 0?

3. IF([Contract Seq #]@row >= 10,

The referenced column "Contract Seq #" does NOT contain a formula.
So, count of cell references = 0?

4. "R" + [Loc {H} # Only]@row + [Contract #]@row + [Contract Seq #]@row, "")))

The referenced columns "Mac Loc", "Contract #", "Contract Seq #" do NOT contain formulas.
So, count of cell references = 0?

Total = 0???

Genevieve P. · 09/06/23

Hey @Mike Meyer

It's hard to know exactly what may have caused the original error without seeing both the Source and Destination sheets (which I would not recommend sharing in the public Community). If you're still having trouble with these sheets after adjusting your formulas, you may want to reach out to Support or book a Pro Desk session if your account has access to it.

I agree that the Reports > Pivot Sheets > formulas looking at Pivot sheets should not play into this specific error.

You're correct with your most recent comment that the referred formula count would be 0, since all [references] point to a cell that does not have a formula... but keep in mind that every cell with a formula adds to sheet complexity in general as well.

In your destination sheet, you say that there are 3,527 rows. Do any of your formulas contain a [Column Reference]:[Column Reference], looking at another formula on that sheet? That would be 3,527 x 3,527 = 12,439,729 referred references, since each of the 3,527 cells down one column are looking at each of the 3,527 cells down another column. Any hidden columns with a formula like this?

Formulas and Functions

Formulas and Functions

Formulas referencing too many cells on source or destination sheet 👎

Best Answers

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

Answers

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

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

When a specific value present, calculate date from Created to Date of completion

What formula do i need?

Nested If statements