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

Joseph Aloysias

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.

Paul Newcome

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

Joseph Aloysias

@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

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.

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

@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)))

Genevieve P.

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?

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!