Forum Discussion
Solved
Getting this msg "Excel ran out of resources while attempting to calculate one or more formulas."
I am having issue with excel sheet which keeps showing me this issue "Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas can not be evaluated."
Never had seen this before- pls guide.
In cell D36 you have formula =30:294001 which means you instruct Excel to return all rows from 30 to 294001. Includes row in which the formula is. Kind of cyclic reference and never ended loop.
If you remove this formula it works.
SergeiBaklanI am also getting that error. Could you please take a look and advise/ fix anything you see wrong.
- Thanks so much for your speedy help and diagnosis!
- Cannot move from cell to cell without an error message showing- 'excel ran out of resources'. Can you please advise??????
In cell D36 you have formula =30:294001 which means you instruct Excel to return all rows from 30 to 294001. Includes row in which the formula is. Kind of cyclic reference and never ended loop.
If you remove this formula it works.
Sergei you seem to be quite proficient in resolving this known Excel issue. I have a co worker who has completely formatted their excel sheet and still receiving this memory issue error message. I know your replies are from a couple years back, but I wanted to see if you could assist me as well.
Hello Chris,
Try to find whole column references in formulas, such as $D:$D, and replace them by their actual boundaries, for instance $D1:D$2000. That should go a long way towards getting rid of the error message.
Please note you can use a third-party tool for this.
Can someone please help me, I have been getting this error recently and i have searched every sheet but cant find what is causing it. When i first released this sheet to my coworkers it had no issues but recently i cant do anything without the error popping up all the time and I don't know what's causing it.
Could someone take a look and see if they can see what im getting worng.
Thanks
Tim
There are whole-range references, such as $D:$D all over the place in hidden sheet "PM-SS Labour location".
Take this formula in H7 : =SUMIFS(' BTTimeClockDataImport'!$L:$L;' BTTimeClockDataImport'!$E:$E;'PM-SS Labour Allocation'!$B$5;' BTTimeClockDataImport'!$D:$D;$C7;' BTTimeClockDataImport'!$S:$S;LEFT($D7;10);' BTTimeClockDataImport'!$C:$C;">="&F$1;' BTTimeClockDataImport'!$C:$C;"<"&I$1)
Just replacing $D:$D (and $E:$E, ...) by $D1:$D2000 (and ...) everywhere goes a long way towards avoiding Excel slow down to a crawl.
Hey there, thanks for all your support in this thread / issue. how would I go about identifying these types of formulas myself. I have a file that's fairly large and in which I cannot remove sensitive info to give to you. SergeiBaklan
It depends. If you have Inquire in your version of Excel you may do Workbook Analysis and check suspicious array formulae. Not necessary that give an answer. Otherwise delete your sheets / ranges one by one till such error disappears. Other words, try to localize it.
