Sheet loading issues - taking too much time to load the sheet

Hi,

I have a order intake sheet where I refer the stock quantity from Item master sheet and grant amount from Grant master sheet. I'm doing most of the calculations(like quantity check and cost check) in the order intake sheet. in the particular sheet I have nearly 16 formula columns and multiple sheet referencing due to that sheet taking too much time to load and sometimes I'm getting error loading page and move row automation is also not working.

Could you please suggest some workaround to manage this.

If I move the data from this to an archive the stock and Cost calculation will not happen.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You can still move rows to an archive and use formulas with cross sheet references to reference the archives.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Joseph Aloysias
    Joseph Aloysias ✭✭✭✭✭
    edited 10/18/23

    @Paul Newcome Thanks for your response I use the below formula to subtract item stock from the initial stock(Total stock) when the Item collected check box is checked, Same way will subtract the cost from the total cost from the individuals allotted account,

    Can you please guide me how to refer the archived sheet?

    =[Initial Stock]@row - SUMIFS([Total Quantity Requested]$1:[Total Quantity Requested]@row, Collected$1:Collected@row, 1, [Item_ID]$1:[Item_ID]@row, [Item_ID]@row)

    =[Purchaser Grant]@row - SUMIF([Purchaser Grant ID]$1:[Purchaser Grant ID]@row, [Purchaser Grant ID]@row, [Total Price]$1:[Total Price]@row)


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    My suggestion would be to have a sheet that has each item listed only once. Then you can use cross sheet formulas there to reference the form sheet to keep your totals. It would be the same formulas but with cross sheet references for the ranges.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Joseph Aloysias
    Joseph Aloysias ✭✭✭✭✭

    Can you please help me with the formula? Also I use this sheet to reports like the stock before and after and the same for grant before and after. How to do reporting? please advise.

  • Joseph Aloysias
    Joseph Aloysias ✭✭✭✭✭
    edited 10/31/23

    @Paul Newcome Can you please help here, the request sheet is almost full and formula referencing is not working now. If I archive the collected items how can I reference that sheet to computation. Please help!

    =IF(Collected@row = 1, [Total Quantity Requested]@row + [Final Stock of Inventory]@row, INDEX({Items Pricing Catalog Master - Final stock}, MATCH([Item_ID]@row, {Item ID}, 0)))

  • Hi @Joseph Aloysias

    What are the errors you're getting? It looks like one of them is "Invalid Reference" - can you check to ensure the {references} are pointing at the right columns?

    The other thing to check is what you're adding together:

     [Total Quantity Requested]@row + [Final Stock of Inventory]@row

    If you do just this formula, do you get an error?

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Joseph Aloysias
    Joseph Aloysias ✭✭✭✭✭

    @Genevieve P. , I appreciate your reply. The invalid reference error was resolved by addressing the reference limit exhaustion. Following @Paul Newcome's advice, I moved the old data to a different sheet and established references to that sheet in the calculation formula. As a result, the solution is now stable, and there is an improvement in sheet performance. Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!