Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF, MATCH, INDEX Memory Hogs?
I have what I think is a simple spreadsheet, about 60 columns and 30 lines,
in each of 3 tabs. My second and third tabs are just where I dump exported data from another program. No formulas, just raw numbers with minimal formating. My first tab is where I summarize my dumped data. I use a combination of SUMIFs (to match up imperfect column alignment, like to match column months, etc.) on a lot of the data. (The data columns are months 1-60, rows are account categories.) I also use INDEX and column numbers to point to specific numbers, plus also a few lines of MATCH in order to grab the correct line for some categories. Anyway, this simple workbook is very sluggish. When I want to insert rows, copy formulas across, etc., it moves very slow, sometimes to the point where it seems to freeze and I either leave it alone for 20 minutes or I have to End Task and reopen Excel. I've started stripping out the MATCH and INDEX formulas (going back to manual pointing), but keeping my SUMIFs, and still it is very slow. The workbook is about 11 Mb, with no graphics. Question (finally!): Are these formulas memory hogs? Should I use them sparingly? Could something else be wrong? Thanks for your help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF, MATCH, INDEX Memory Hogs?
11 Mb is huge for an Excel file. One possibility, in the second and third
sheets, try hitting Ctrl and End on the keyboard to see where Excel thinks the last used cell is. If it's a cell way down the sheet which looks empty, below the end of the data you have, highlight all the rows between the Ctrl End cell and your last cell and delete them,(delete, not clear contents). Do the same with the columns, save and reopen, see if the file is any smaller. Regards, Alan. "KCobler" .(donotspam) wrote in message ... I have what I think is a simple spreadsheet, about 60 columns and 30 lines, in each of 3 tabs. My second and third tabs are just where I dump exported data from another program. No formulas, just raw numbers with minimal formating. My first tab is where I summarize my dumped data. I use a combination of SUMIFs (to match up imperfect column alignment, like to match column months, etc.) on a lot of the data. (The data columns are months 1-60, rows are account categories.) I also use INDEX and column numbers to point to specific numbers, plus also a few lines of MATCH in order to grab the correct line for some categories. Anyway, this simple workbook is very sluggish. When I want to insert rows, copy formulas across, etc., it moves very slow, sometimes to the point where it seems to freeze and I either leave it alone for 20 minutes or I have to End Task and reopen Excel. I've started stripping out the MATCH and INDEX formulas (going back to manual pointing), but keeping my SUMIFs, and still it is very slow. The workbook is about 11 Mb, with no graphics. Question (finally!): Are these formulas memory hogs? Should I use them sparingly? Could something else be wrong? Thanks for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
INDEX / MATCH with SUMIF/COUNTIF/SUMPRODUCT Statements? | Excel Worksheet Functions | |||
Match& Index and Sumif | Excel Discussion (Misc queries) | |||
offset? match index? sumif? 2way look-up? | Excel Worksheet Functions | |||
SUMIF linked WBs giving OUT OF MEMORY | Excel Worksheet Functions |