MS Excel (Lookups slowing performance) 1

[DSerr77]

I have created multiple reports that look to an external data source(another excel workbook) that use Hlookups, Vlookups and Sum ifs to populate the reports based on specified criteria. The reports work fine except that excel slows to a crawl (to the point of being prohibitve). Is there anything I can do to speed up the file's performance?

 

[xlhelp]

See if this helps:

http://www.decisionmodels.com/optspeedd.htm

 

[byundt]

One simple step you can take is to turn off automatic recalculation while you are doing data input & editing. Then trigger a manual recalc with the F9 key when you want to look at the results. You can do this using the Tools...Options...Calculation menu item.

It is possible to have calculation turned off whenever your workbook is active, then turn back on when you close (or leave) the workbook. Doing so requires using VBA macros.

Brad