Simplification of Formulas for Formula Cell Reference Limit

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.

Answers

  • Andrée Starå
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • @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å
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!