If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel cannot complete this task with available resources
I have a presentation spreadsheet (SS) that until recently worked fine.
It requests the user enter a recipe by listing codes and quantities. By VLookup it then pulls all the relevant descriptions, costs etc from another data SS. The data SS has over the last year doubled in size and I now get the Excel cannot complete this task with available resources..... message unless I have the data file open first. Can I force the presentation SS to open a copy (preferably hidden in the background) of the data SS to prevent this or is there an alternative. I have minimised the amount and complexity of my formulae to try and avoid this but cannot think of an alternative method. Thanks Last edited by PSM : June 12th, 2009 at 04:07 PM. |
#2
|
|||
|
|||
Excel cannot complete this task with available resources
From what you say, the data workbook is just going to keep on getting bigger,
so anything you can do to stave off the inevitable end is just temporary. I can think of only two longer-term solutions: 1) If you can, you might help by going through your data workbook and continually cull out old data. Of course, you may need old data just as much as recent. 2) It may be time to bite the bullet and switch to MS Access. Excel is great for databases that a) aren't too complex and b) aren't too large. Once you start getting over 10 or 15 000 records (more or less depending on how many columns), it's getting time to think about switching over -- and if the database is just going to keep on growing, as most of them do, don't bother going for temporary measures, just do what you have to do. There may be a third possibility. Perhaps you could work out some system where your data is stored elsewhere, in Excel as you're doing it now or even in a text database, and you access it not with VLOOKUP but using a SQL query from within your presentation workbook. That would let you pull just the data you need for this particular recipe without (hopefully) overloading your RAM. But I'm only guessing that it would help, and in any case you've have to learn how to use that method. Or maybe you already know how? --- "PSM" wrote: I have a presentation spreadsheet (SS) that until recently worked fine. It requests the user enter a recipe by listing codes and quantities. By VLookup it then pulls all the relevant descriptions, costs etc from another data SS. The data SS has over the last year doubled in size and I now get the Excel cannot complete this task with available resources..... message unless I have the data file open first. |
Thread Tools | |
Display Modes | |
|
|