Formulas referencing too many cells on source or destination sheet 👎

Mike Meyer

"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

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.

Hey! Thanks for the tag, @Paul Newcome

Was it this thread?

https://community.smartsheet.com/discussion/comment/376183#Comment_376183

Or maybe this one?

https://community.smartsheet.com/discussion/78708/100-000-cell-reference-limit-do-i-understand-it-right-and-can-i-monitor-it

Paul Newcome

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

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.

Hey! Thanks for the tag, @Paul Newcome

Was it this thread?

https://community.smartsheet.com/discussion/comment/376183#Comment_376183

Or maybe this one?

https://community.smartsheet.com/discussion/78708/100-000-cell-reference-limit-do-i-understand-it-right-and-can-i-monitor-it

Mike Meyer

@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

@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

⚡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:

1. 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.
2. 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.

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?