Excel cannot complete this task with available resources

[PSM]

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) 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

 

[Bob Bridges]

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?