• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate a section of a worksheet on button click

M

mnair77

New Member
Hi All,


This is my first post, but I have been following these forums anonymously for quite sometime and have found them extremely helpful.


I have a workbook with some fairly complex formulas on multiple sheets. It currently takes 5-6 seconds to recalculate on editing data. There is no macro usage so far.


What I'd like to do is, to create buttons that will cause some sections of the workbook to be recalculated and populated as values - not formulas, so that recalculation on editing is minimized. I don't like to turn off auto-calculation, as this confuses me a lot when working on other workbooks.


What is the best way to do this, short of recreating the whole thing? Should I keep the formulas as text some other area, and use them for calculations in a macro, populating the results as values in the target cells? How hard is this to do? Any other suggestions?
 
Mnair77


Firstly, Welcome to the Chandoo.org forums.


You may be best to first try and fix the recalc times of the spreadsheet as is.

Simply replacing Volatile Functions with Non-Volatile functions normally fixes that.


I'll redirect you to:

http://chandoo.org/wp/2012/03/27/75-excel-speeding-up-tips/

and

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


Past that it is possible to add some buttons to copy/paste formulas with the values

and to reconstruct the formulas if that's what you want.
 
Hui, Thanks for your quick response! Apologies for the multiple posts that got created - I'm having trouble with the proxy. I request a moderator to delete the duplicate posts.
 
The duplicate posts were deleted before you wrote your response!
 
You must log in or register to reply here.
Back
Top