Saving Excel file locks up all memory resources, accesses externalfile?

[benscordell]

I have a large (17MB) Excel file that locks up all the memory
resources (2GB) that my machine can throw at it when I try to save it
(save and/or save and close). Specifically, it has a few steps:
1. Calculates the formulas - the status % complete at the bottom left
usually runs through this stage fairly quickly
2. Save progress bar - progress usually moves rapidly until the 80 -
90% complete range before
3. A new Windows Explorer instance appears with the Excel icon and the
name of the file I am trying to save being displayed. This step takes
a while (6 - 16 minutes) to resolve.

I have minimized the number of competing memory resources involved
(close all other applications, increase virtual memory) as well as
optimized my formula choices (vlookup instead of arrays) to little
avail.

Any thoughts?

Thank you!
Ben

 

[refresh]

Hi Ben,

I have found that using Index & Match can be faster than Vlookups. If you
want to post a copy of a vlookup you use I could try and translate into
Index/Match. Alternatively run a search in this forum for index or match and
you will probably find something you can adapt.

Cheers

Refresh

 

[benscordell]

Hi Ben,

I have found that using Index & Match can be faster than Vlookups. If you
want to post a copy of a vlookup you use I could try and translate into
Index/Match. Alternatively run a search in this forum for index or match and
you will probably find something you can adapt.

Cheers

Refresh








- Show quoted text -

Thank you for the feedback; a vlookup example is included below. I
also make extensive use of SUMIF, which may be partially to blame as
well.

Do you know why Excel would open up/access another spreadsheet during
the save process? Is there a good way to search for external links?
I'm fairly certain I removed all external links, but maybe I missed
something.

=IF(ISERROR(VLOOKUP($B16,'Consolidated_Data-1-Time'!$B$8:$W$2600,K
$4,FALSE)),"",(VLOOKUP($B16,'Consolidated_Data-1-Time'!$B$8:$W$2600,K
$4,FALSE)))

Thanks again, Ben

 

[refresh]

Hiya,

You can check for links through Data - Edit Links, my machine was looking
for 'Consolidated_Data-1-Time' as a file.

Will look at formula and get back to you later if ok.

 

[refresh]

hi again, apologies for delay.

I'm not sure where the K4 fits in rather than using a column ref in the
formula. It's thrown me a bit. Was also wondering whether you need the
ISERROR part of formula, it just replaces the error code with a blank, if you
are presenting the spreadsheet I can see why you'd do that but if its to
allow filtering you could probably live without it.

Finally, if you are using a lot of lookups have you considered using Access
instead - it's much more appropriate platform for matching things up. Using
Index/match in retrospect may not make too much difference in timing. Is
there any data you could take out of file?


Regards