Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving Excel file locks up all memory resources, accesses externalfile?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving Excel file locks up all memory resources, accesses external
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 " wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving Excel file locks up all memory resources, accessesexternal
On Dec 18, 7:02 pm, refresh wrote:
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 " wrote: 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- Hide quoted text - - 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving Excel file locks up all memory resources, accesses exte
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. " wrote: On Dec 18, 7:02 pm, refresh wrote: 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 " wrote: 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- Hide quoted text - - 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving Excel file locks up all memory resources, accesses exte
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 "refresh" wrote: 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. " wrote: On Dec 18, 7:02 pm, refresh wrote: 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 " wrote: 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- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel should let me sort the file directory when saving a file | Excel Discussion (Misc queries) | |||
Saving an excel file without it amending the name of file name | Excel Discussion (Misc queries) | |||
file "save as" to floppy locks up | Excel Discussion (Misc queries) | |||
Excel data query locks source file | Excel Discussion (Misc queries) | |||
How do I stop Excel 2000 from saving file history from file that . | Excel Discussion (Misc queries) |