Is my Spreadsheet too big??

**easty** · 08-09-2010, 11:12 PM

Hi There,

I've finally come close to the end of constructing my spreadsheet.......it is 22.1MB.

It appears as though the file size is too big and the spreadsheet is very clunky when it runs.

There are all sorts of macros and crazy links all over the spreadsheet. There are about 180 worksheets each with unique bits of information that are linked to central summary sheet via =indirect("'&c2&"","a1") etc formulas.

As an example of what doesn't work properly an error message appears when I try and create a new workbook .....the message just says that "excel cannot open or save any more documents because there is not enough available memory or disk space" etc etc.

I'm looking at my processes in the Windows Task Manager and it says that EXCEL.EXE is using about 178,000K........is this a lot?

Would this have something to do with the RAM on my computer? or could the actual file be corrupt?? or another issue?

If someone could please provide some guidance that would be greatly appreciated. Its taken me about 6 months to build this spreadsheet (with a great deal of assistance from this website....thanks heaps!!!!!) and I would be very unhappy if it failed at the last hurdle.

thanks again

easty

**royUK** · 08-10-2010, 01:40 AM

INDIRECT is a volatile Function and each one will re-calculate at each calculation.

I can't see the benefit of 180 sheets.

**easty** · 08-10-2010, 04:00 AM

there are essentially 170 different clients that I'm using data for, which means that each client has to have there own worksheet......

there are therefore a heap of different indirect formulas in a summary sheet drawing the data from each client's spreadsheet. Would it be better just to have a direct reference to each client spreadsheet instead of using the indirect formula??

cheers,

easty

**royUK** · 08-10-2010, 04:21 AM

I wouldn't have individual sheets. I would have one data sheet with all the data in a table, Column A being a list of clients. Then you can use AutoFilter, AdvancedFilter or PivotTable to make a report sheet

**DonkeyOte** · 08-10-2010, 04:25 AM

Just reiterating Roy's point - if you use XL for data storage you should think "database"

You wouldn't have 170 duplicate tables in your DB differentiated only by name you would have 1 table with an additional field to denote client id - same logic applies in XL.

**easty** · 08-10-2010, 09:13 AM

each worksheet for each client is updated monthly and includes performance calculations and cashflows amounts etc. so each worksheet is very different to the next.

I'm not sure if using an alternative to excel is possible.

when running the spreadsheet the processes in my task manager show excel running at about 250,000K, which is well in excess of other processes.

does this seem like a lot? could the file potentially be corrupt?

**Shaner73** · 08-10-2010, 01:02 PM

Maybe you could use Access.

**easty** · 08-10-2010, 10:25 PM

would access allow me to do calculations based on updated portfolio values etc?

**royUK** · 08-11-2010, 01:18 AM

It's difficult to make suggestions without seeing your requirements. It should be possible to have one sheet for the calculations, populate it with the relevant clients details from the master data sheet, VLOOKUP should do this