How to make a formula cell dynamically display

[Guest]

Hi all,

There is simple formula =sum(A1:A10. If any of cell in range A1:A10 is
changed, how to make the cell which contain the formula display the change?

Clara

 

[David Biddulph]

Tools/ Options/ Calculation:
Make sure it says Automatic, not Manual.

 

[Mark Lincoln]

To add to David's reply, if calculation is set to manual you can
recalculate at any time by pressing F9.

 

[Guest]

Hi David,

It works well. Another questio is that if this workbook is opened by another
Excel, does the setting keep going? or How can I make the setting go with the
workbook.

Clara

 

[Gord Dibben]

Clara

The calc mode is set for that session of Excel by the the mode from the first
workbook opened.

If you save the workbook with calc mode set to "Auto" it will open that way
unless another workbook with calc mode set to "Manual" has been opened first.

No way around it unless you want to set the calc mode in Workbook_Open code.

Private Sub Workbook_Open()
Application.Calculation = xlCalculationAutomatic
End Sub
Right-click on the Excel Icon left of the "File" on the menubar or on the Excel
Icon at left end of Title Bar if not maximized.

Select "View Code"

Paste the above into that module.


Gord Dibben MS Excel MVP

 

[Tyla]

Making the setting go with the workbook would be tougher. The issue is
that the calculation setting (e.g. manual, automatic) is an
Application property not a workbook level one. That is, it applies to
all workbooks open in an Excel tsession (and subsequent sessions) not
just a particular workbook open in a particular session. If your
comfortable with macros, you can write a short one detect and store
the current calculation state (e.g. "bCalcState =
Application.Calculation") and if it's not what you want for your
workbook, then ask the user if they'd like to change it. If you do
this, though, you should change it back to its original state when you
close the workbook. There may be a reason, after all, a particular
user likes their Excel to be in a particular calculation mode. [And
even setting it back to the state it was when you opened the workbook
would ignore/overwrite any changes the user made manually during that
Excel session. You could do a second check of its state before you
close your workbook o trap this situation or, if you assume this kind
of change is a very low probability event, you could change it back
without asking.]

HTH
/ Tyla /