Getting this msg "Excel ran out of resources while attempting to calculate one or more formulas."

mg2324 

Yes, applying any filter I receive such error as well. I tried to play with variants and setting, but so far I didn't find the way how to correct this, if only rebuild workbook. But it is huge.

Lot of formulas which use entire columns for calculations, INDIRECT, stack of many sheets,...

One-processor calculation option also didn't help.

Will try later to play bit more

Office 365 - Tried so far on Excel versions 2210 and 2211 (Monthly Enterprise Channel). 

Sometimes the error happens right when I open it.  Other times it will be randomly when I update something on a tab.  I've noticed it does it frequently when I go to the QV Inventory tab and filter.

mg2324 

Okay, I got your file, on opening and recalculation I see no one error. On which Excel platform/version you are which gives an error?

Hi I am having a similar issue. I sent you a direct message. Please let me know if you are able to assist. Would very much appreciate your help. Thank you.

Yuliya1610 

Hope it'll be sorted out. I deleted the attachments, in addition you may destroy your link. 

Thank you so much !!! I am really grateful !!!
I corrected my spreadsheet and sent it to the person with corporate Office 365, I will receive an answer how it works maybe in several days.
(I can suppose, when Excel does not see the exact date, less than which the value should be, it goes looking for it at the very beginning of time ))) , and this generates such an error. It's strange why it only appears in 365 office and does not in 2016.
Thank you for attachments, I downloaded variant with formula.
If it is possible, could you please delete attachment now (because I don't want this file to be freely available on the Internet).
Best regards, Yuliya

Hi Yuliya1610 ,

Error appears in InventoryReport sheet with formulae in column G (Qty)

=SUMIFS(
    ProducedGoodsTracker!$D$3:$D$10000,
    ProducedGoodsTracker!$B$3:$B$10000, $B4,
    ProducedGoodsTracker!$A$3:$A$10000, "<" & $D$1
) -
    SUMIFS(
        SalesTracker!$D$3:$D$10000,
        SalesTracker!$B$3:$B$10000, $B4,
        SalesTracker!$A$3:$A$10000, "<" & $D$1
    )

if D1 (start date) is blank. Don't know what is exact reason, perhaps will return back to that some later. As workarounds you may

- remove data validation from D1, enter any date, e.g. 1900-01-01 instead of blank and apply data validation again. However, if someone Del on D1 an error appears again.

- more reliable to modify formula like

=SUMIFS(
    ProducedGoodsTracker!$D$3:$D$10000,
    ProducedGoodsTracker!$B$3:$B$10000, $B4,
    ProducedGoodsTracker!$A$3:$A$10000, "<" & IF( ISBLANK($D$1), 0, $D$1 )
) -
    SUMIFS(
        SalesTracker!$D$3:$D$10000,
        SalesTracker!$B$3:$B$10000, $B4,
        SalesTracker!$A$3:$A$10000, "<" & IF( ISBLANK($D$1), 0, $D$1 )
    )

Both variants are in attached, sheets protection is removed.