x404.co.uk
http://www.x404.co.uk/forum/

I think I just killed Excel :D
http://www.x404.co.uk/forum/viewtopic.php?f=4&t=2135		 Page 1 of 1
Author:  Fogmeister [ Wed Aug 05, 2009 12:43 pm ]
Post subject:  I think I just killed Excel :D
I've got a workbook open in excel and I've been adding to it today (but I haven't saved it).

The workbook has 10 worksheets and the first one contains a summary table based on the entries in the other 9 worksheets.

It's a database table comparison thing. The summary lists the fields used in a given program (one of the 9 worksheets) and populates a table showing which of those fields are used in the other programs (the other 8 worksheets).

I'm actually quite proud of it :D

Anyway, I think by not saving it it was starting to use up too much memory or something as it was beginning to take up to 10 seconds to populate the summary table :D As soon as I saved it it went back to doing it instantaneously.

Never seen that happen before. (It also told me how far through the calculation it was and how many processors it was using).
Author:  Fogmeister [ Wed Aug 05, 2009 1:03 pm ]
Post subject:  Re: I think I just killed Excel :D
Ooh, it was also coming out with incorrect values from formulas.

I went through evaluating a formula and the last step was...

=IF(TRUE,"","X")

This should display blank as the value if true comes after the first comma.

The next and final evaluate step gave the final answer as X ?!?!
Author:  RedEyes [ Thu Aug 06, 2009 8:32 am ]
Post subject:  Re: I think I just killed Excel :D
Is that formula fixed yet, or was it just an oddity due to not saving?

If not, what is the entire formula?

I always make a point of saving regularly these days - where I used to work we had Office 2000 (no autosave!), and I've seen too many spreadsheets, and hours of work, go west after a power flicker. Autosave is all very well, but I prefer a proper saved copy, just for peace of mind.

I'm struggling with a sheet here at the moment myself - I think it's too complex! Even freshly opened it takes about a minute to calc. I think I might have to redesign it to import figures via a macro rather than using live data. That's a helluva lot of work. :(
Author:  AlunD [ Thu Aug 06, 2009 12:12 pm ]
Post subject:  Re: I think I just killed Excel :D
RedEyes wrote:
I've seen too many spreadsheets, and hours of work, go west after a power flicker. Autosave is all very well, but I prefer a proper saved copy, just for peace of mind.


Oh yes oh Yes that has been the cause of more outbursts of fowl language, periods of high blood pressure, and head banging on walls than virtually anything else. :roll:
Author:  EddArmitage [ Thu Aug 06, 2009 12:27 pm ]
Post subject:  Re: I think I just killed Excel :D
AlunD wrote:
fowl language

Cock a doodle doo!
Author:  Fogmeister [ Thu Aug 06, 2009 3:39 pm ]
Post subject:  Re: I think I just killed Excel :D
RedEyes wrote:
Is that formula fixed yet, or was it just an oddity due to not saving?

If not, what is the entire formula?

Yeah it's working now.

The table was made up as follows...

The left hand "Row header" (if you like) was the formula...

=IF(OFFSET(INDIRECT("'"&$C$4&"'!$B$1"),ROW(A8)+$C$3-9,0) = $C$5, OFFSET(INDIRECT("'"&$C$4&"'!$C$1"),ROW(A8)+$C$3-9,0), "")

Then there are 8 columns with the formula...

=IF(ISERROR(VLOOKUP(CONCATENATE($C$5,".",$B8),INDIRECT("'"&C$7&"'!A:C"),3,FALSE)),"","X")

In total there are around 150 rows in the table and it's accumulating data from 9 other worksheets :D
Page 1 of 1 All times are UTC
Powered by phpBB® Forum Software © phpBB Group
https://www.phpbb.com/