Simplification of Formulas for Formula Cell Reference Limit

Alex.Charles

Hi All,

I recently reached this limit for a sheet that was implemented. I have 6 columns that pull from a reference sheet. For example: =INDEX({MWN Ref: Supervisor}, MATCH([@Email]@row, {MWN Ref: @Email},0)). I don't believe that those columns are the issue.

I have two separate columns that I will paste below that I need help to simplify in order to continue their use since they are involved in several automations and are a key part of the sheet. I believe these are causing the issues. I hit this limit around 2600 rows, so I ended up temporarily eliminating one of the column formulas to maintain sheet function while I work towards a solution. Each of these formulas are supposed to calculate those sums for each unique "Email" entered (approx. 1000).

Total Hours Used for the current calendar year: =SUMIFS([Hours Used]:[Hours Used], [@Email]:[@Email], @cell = [@Email]@row, Date:Date, @cell <= Date@row, Date:Date, YEAR(@cell) = YEAR(Date@row))

Total Points for Last 365 days: =SUMIFS([Points Assigned]:[Points Assigned], [@Email]:[@Email], @cell = [@Email]@row, Date:Date, @cell >= TODAY(-365), Date:Date, @cell <= Date@row)

I hope that at some point SS can increase the limits on these sheets for larger scale applications, but that seems like a far off dream at this point.

Andrée Starå

Hi @Alex.Charles

I hope you're well and safe!

There will be updates to the limits coming this year (if nothing gets delayed)

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Alex.Charles

@Andrée Starå, I appreciate that information! Do you have any info on the specifics? How much of an increase we're looking at or approximate timeline (Q3/Q4)?

Thanks!

Andrée Starå

@Alex.Charles

Happy to help!

• Many complex formula computations are now running 10 times faster. 
• Early next year, Control Center will support 20,000 active projects per blueprint — that’s up 20x year over year — for unparalleled project portfolio management.
• In the second half of 2024, each sheet will support five times more scale than they could previously, bringing sheet capacity up to a million rows, with five million cells, and five million sheet links per sheet. That’s a 250x improvement to cross-sheet cell linking, which plays an instrumental role in aggregating data across sources on your portfolio. 

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!