Help

  • Thread starter Thread starter Ron
  • Start date Start date
R

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!
 
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)
 
Back
Top