I'm pretty sure this can't be done but I thought i'd check here just in case!
Does anyone know if it's possible to have some cells in a workbook set to manual calculation and some set to automatic?
I'm just trying to speed up a workbook which had loads of SUMIFS functions that only need to be recalculated once every month.
Thanks,
Alice
Not that I know.
One workaround that you could try if - if you're sure that no other cells are dependent on these formulas - is to do a bulk Find and Replace on all of them, replacing "=" with e.g. "#". That will convert them to text entries and so obviously stop them calculating. Then, when you need to use them again, you can replace back all the "#" with "=" which will force Excel to recognise them again as formulas.
Be warned, though, if it's a very large number of formulas that we're talking about converting, then the conversion back to formulas via this Find and Replace method can be quite heavy on resources.
Regards
Annoying that it can't just be set but that's a great idea, thank you!
to the best of my knowledge, sumifs() only calc if there has been a change. I would look at what else might be slowing down your file?
Look for volatile formulas like today(), array functions, ranges in formulas that reference entire rows/columns, Conditional Formatting that spreads across areas that do not need it. Check to see that you dont have regular formatting that you dont need - hit end/home and see where you end up. If its "out in the middle of nowhere", then delete all unneeded rows and/or columns (carefully though)
These are just a few things to look at. Take a look at this site for additional suggestions
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Under formulas Tab you have the option to tell Excel NOT to calculate anything till you override
You can choose from Automatic, Automatic except Data Tables and Manual under Calculations Options Drop Down in Formula Tab. Set it for Manual and then when you are ready for update press F9
From Microsoft Help centre
F9
Calculates all worksheets in all open workbooks.
F9 followed by ENTER (or followed by CTRL+SHIFT+ENTER for array formulas) calculates the selected a portion of a formula and replaces the selected portion with the calculated value.
SHIFT+F9 calculates the active worksheet.
CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.
CTRL+ALT+SHIFT+F9 rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated.
CTRL+F9 minimizes a workbook window to an icon.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks