Help

[Ron]

I have a spreadsheet that is approx. 10MB. It is approx.
27,000 rows of data extracted from an accounting system
that I am then trying to manipulate via additonal sheets
and formulas.

I usually use a pivot and simply copy and past special
value screen shots into other sheets. In an effort to make
things more efficient for me I have tried to write some
straightfoward formulas/functions into the spreadsheet.

It has a fair amount of simples links, VLOOKUPS and
recently I added some conditional sum formulas. With all
these whistles and bells (which work great) the
spreadsheet takes 15+ minutes to calculate, that is if it
doesn't shut down at some point during calculation.

10MB is not real big in my estimation, I can't believe the
file size is what is slowing me down.

I love using VLOOKUPS anf SUMIF and Conditional Sum type
formulas. Is it possible that those formulas aren't good
to use when your dealing with 27,000 lines of data. I am
stuck going back to the Pivot and simply copying that data?

Any help or thoughts would be appreciated, thanks!

 

[Nick Hodge]

Ron

All the lookup and conditional aggregate functions are slow and that is
undoubtedly your issue. Have you considered using your pivot table to build
a sub-pivot (In other words use the same data to set up a different subset)
or simply double clicking the data you require in the pivot table which
automatically extracts that data to a new sheet

Just a couple of thoughts

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)