Forum Discussion
GaneshIyer
Dec 19, 2020Copper Contributor
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."...
- Dec 19, 2020
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.
SergeiBaklan
Dec 19, 2020MVP
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.
- Chris_ValadezJan 03, 2024Copper Contributor
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.
- Stephane_ZwelJan 03, 2024Copper Contributor
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.
- Chris_ValadezJan 03, 2024Copper Contributor
Hey Stephane,
This error occurs even within fresh Excel sheets through simple data entry with no attached formulas. Would using your solution resolve an issue like this?
Thanks
- TimMcBrideCanadaFeb 09, 2023Copper Contributor
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
- Stephane_ZwelFeb 15, 2023Copper Contributor
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.
- SergeiBaklanFeb 13, 2023MVP
TimMcBrideCanada , I opened the file and had no error alerts. Which exactly error do you have and how to reproduce it?
- BSinisiNov 03, 2022Copper Contributor
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
- SergeiBaklanNov 13, 2022MVP
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.
- SontiyiNov 02, 2022Copper Contributor
Hey, I'm suddenly having this issue, I do have one crazy sheet full of sumifs formulas but I don't think this is an end-of-game excel workload. Can you please take a look at my spreadsheet? The sheet that gives trouble is "DCF Flujo". You can trigger the problem by cutting rows 179:183 and pasting them at row 143. That's what Im trying to do and can't.
Much appreciated!
- SergeiBaklanNov 13, 2022MVP
I'm not sure which exactly formula gives an error, but if you add in AU1 next month (Jan 01, 2023) it works.
- semeltonOct 26, 2022Copper Contributor
- IsabelRDZSep 27, 2022Copper Contributor
SergeiBaklan I try to check and I realize that cell D36 is clear in all my documents. In my case, that message appears every time I open excel, and every time I type anything, so annoying! I'm not using excel for any formula. I even went there and format the cell as text. This issue just started and I don't know why. If you could help me, I will really appreciate it.
- SriSulis_IDJun 07, 2022Copper Contributor
Hi SergeiBaklan , how we know from which area that the error value come, that cause the message pop up?
- SergeiBaklanJun 08, 2022MVP
Inquire, if you have it in your version of Excel, could help with identification.
- clarissa79Feb 28, 2022Copper Contributor
SergeiBaklan All of my spreadsheets show this error and I am not using formulas. This issue just began for me. I have my cells marked as "General" as input type and every time I delete, tab over, press enter, etc. the error pops up. I am simply trying to keep a list of attendees for an event with some details (emails, arrival dates, etc.). Any tips/settings? Thanks!
- Jackie_JackieMar 16, 2022Copper ContributorSergeiBaklan Similar to other ppl on this thread, I have spent hours trying to find the issue with the same error that other people had, and the Excel is extremely slow. Would you be able to have a look?
- SergeiBaklanMar 16, 2022MVP
I'll try if you share the file
- EdelLauOct 12, 2021Copper Contributor
Hi I seem to have the same problem too. I tried to find the loop or error but I still don't know why the message is popping up. Are you have to have a look? I suspect it's in the data worksheet.
- SergeiBaklanOct 23, 2021MVP
- Arthur2075Oct 19, 2021Copper Contributor
you seem to be a wizard at these, can you have a look a this one with the same error please.
i have tried the error check and it says its fine, but it is not. also i do not have the inquire tab?
any help is much appreciated, sorry for the hijack!
- SergeiBaklanOct 23, 2021MVP
- JohnG0213Sep 08, 2021Copper Contributor
Hello, I've been getting the same error on multiple sheets. Hoping you can help me out with this.
- SergeiBaklanSep 08, 2021MVP
In both files NewQuotationCfm!O11 (commented)
- TomylorSep 09, 2021Copper Contributor