I have a database of some 450,000 records consisting of about 10 fields per record - mix of text/number and date fields

Using Excel 2007, I can potentially put all these records on one sheet, which makes the formulas simpler of course but is not essential.

On the record sheet I run a number of calculations (15 or so) and then use a front sheet to display grouped results based on IF statements (countifs/averageifs etc)

All pretty simple stuff but the problem is that the size of the spreadsheet grows enormously! Currently 45mb.

What tips/tricks to keeping the size as small as possible? Should I use external links (other workbooks) or maybe an access db (the data is a result of a complex sql query otherwise I could reference the sql db directly). Keep it on 1 sheet? Anything?

Cheers!!

Puff

Puff

 

Simple thing you've probably looked at - is "track changes" off? Used to cause a lot of file size issues.

 

Its off

(Had never thought to look lol)

 

I would go to at least an access DB if not SQL server. It would depend on how business critical it is !

 

Agreed.

Access as a minimum
Wouldn't use Excel for anything over a few thousand rows myself.

Can you not use the Source SQL DB itself?

 

excel sucks for that sort of stuff, get an access db, much faster and smaller!

 

SQL is the way forward, access sux to!

 

OK - going to try Access.

So what is

=IF(A1="","",IF(A1=DATE(1900,1,0),"",IF(B1=DATE(19 00,1,0),A1,IF(B1>A1,B1,B1))))

In Access?

Two date/time columns which may or may not be blank & I'm asking if B1 exists, if its before A1 put A1 and if it exists and is after A1, put B1.

 

SELECT
IIF
(DATEDIFF("D",
IIF(IsDate([DATE-A]),[DATE-A],"01/01/1000") ,
IIF(IsDate([DATE-B]),[DATE-B],"01/01/1000")
)
<= 0 , [DATE-A] , [DATE-B]) AS [RETURNED-DATE]
FROM [DATA-TABLE];

Will work from year 1000 upwards only

 

Woah

I feel a whole new learning curve coming on here! I knew there was a reason I stuck to Excel...

If this was stuck in column C in the db would it work it out for all the records individually? I presume this is run as a query somehow..

Gah - head time...

 

Just run that as a query along with the other columns you want to return ...

if you create a table in access and let me have the columns names it will be v easy to recreate the query

Give us a shout if you want some help !

 

YHM!