Excel cannot complete this task with available resources.

[herkiefan]

Hi,

I store a large amount of data in one workbook (BON DATA) comprised of 3 worksheets (Revenue, Payroll, Rated Play) and reference this data using another workbook (DOR Model).

I recently modified my BON DATA workbook by adding a couple of columns to the Revenue worksheet and began recieving the following errors in my DOR Model:

"Excel cannot complete this task with available resources. Choose les data or close other applications." OK-button only.

Followed by:

"Unable to save external link values." OK-button only.

I utilize the VLOOKUP function and have 3 basic ranges that I reference:
* Revenue: 1055 rows x 192 columns = 202560 cells
* Labor: 1102 rows x 159 columns = 175218 cells
* Rated Play: 2082 rows x 70 columns = 145740 cells

When I open my DOR Model, I get the above errors and also each cell that references the Revenue range contains #REF! and those cells that reference either the Labor range or the Rated Play range contain #N/A.

When I open the BON DATA workbook, the values are then filled in.

I have searched Microsoft and found that the error can be caused by referencing a range in another workbook that is larger than 16,000 cells.

However, I only added 16 columns (3072 cells) to my original Revenue range (199488 cells). My original range was far and away greater that the supposed 16,000 cell limit.

Other than breaking my 3 main ranges into smaller, < 16,000 cell, ranges does anyone have advice for me?

Thanks,

Mike

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[LNBruno]

I've hit this a number of times with a number of different applications. If you've got a bunch of processes running (a lot of open windows), that may be causing it.

Try this: CTRL+ALT+DEL, then click on the Task Manager button, then select the Processes tab in the Windows Task Manager and finally, click on the Mem Usage column header twice (to bring the biggest memory hogs to the top of the list). That should give you a better idea of what may be eating up your resources.

< M!ke >

 

[herkiefan]

I've checked the task manager and I have plenty of RAM left.

The file size of my DOR Model is 2,221KB and the file size for my BON DATA is 7,319KB.

When I use the =INFO("memused") function, it results in 36,399,660 bites used when both the DOR and the BON DATA are open. Excel 2003 SP2 has 1 Gig of memory available, so I am no where near the maximum.

Does anyone know what the true range limits are for a VLOOKUP function that references another workbook? Microsoft says the range maximum is 16,000 cells but, I have VLOOKUP's that reference many more cells than that in other spreadsheets that still work.

I do not know why it does not work on this spreadsheet. Anyone else have this problem and have found a solution that works?



“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[LNBruno]

At the risk of being shot down again (and BTW, I'm only trying to help):

1) Office 2003 SP2 causes a file save error if the file is password protected, but Excel reports it as a lack of available resources.

2) My experience is that use of links is extremely memory consuming and could cause this error (it doesn't make any difference how much RAM you have installed, and the file sizes don't matter either.

3) Check out this site for more info on Excel limits:

http://www.decisionmodels.com/memlimitsc.htm

< M!ke >

 

[xlhelp]

Have you looked at MS article 292471?

Member- AAAA Association Against Acronym Abusers

 

[LNBruno]

Yeah, and if you search the MS site for the specified error message, you get 1,077 hits.

Happy hunting!

< M!ke >

 

[herkiefan]

<M!ke>,

I apologize if my tone in my last response was less than cordial. It was absolutely not my intent.

Thank you for sending me the link to the memlimitsc decision model. I read it really close and I found my problem.

It's the number of unique cells that the links refer to can not be greater than 16,000. Not the size of the range.

Thanks for your help.

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein