+ Reply to Thread
Results 1 to 4 of 4

Formulas no longer working

  1. #1
    DK
    Guest

    Formulas no longer working

    Greetings and thank you in advance for any help.

    I have a workbook that is 671K in size and contains about 75 different
    worksheets. The first tab is a summary and details are provided on the
    remaining tabs.

    The problem I'm having is that the summary sheet has stopped updating. The
    formulas are simple (e.g. sum a row, show the contents of a cell, etc.).
    Calculation is set to automatic but I've tried switching it to manual and and
    still nothing.

    The only way a cell will update is if I select a specific cell, F2 then ENTER.

    Any help you might provide is appreciated.

  2. #2
    Niek Otten
    Guest

    Re: Formulas no longer working

    =========================================================

    Cells not calculating (correctly)

    Niek Otten, March 31, 2006



    Most frequent causes:



    1. Calculation is set to Automatic. This often happens unintentionally, for example by opening another workbook first.
    Calculation is an Excel-wide setting; the first workbook opened determines the calculation mode, which then applies to all open
    workbooks and workbooks that are opened later in that instance of Excel. It can be changed manually (again, for all open
    workbooks):
    Tools>Options>Calculation tab, check Automatic.

    2. There are User Defined Functions (UDFs) which access cells directly from within the function, that is, not via the
    argument list. Then Excel is not aware of the need of recalculation if the precedent cell changes. You can include
    Application.Volatile in the function, but there is no guarantee this will always calculate cells in the correct sequence in all
    (future) versions of Excel. It also causes the calling cells (and dependents) to always be recalculated, even if not necessary.
    Really the best way is to include all precedent cells in the argument list.

    3. You see the formulas in the cell, not the answers. Two options: a. Tools>Options>View, Formulas is checked (you may
    accidentally have hit the shortcut key: CTRL+`), b. the cell was formatted as text before you entered the formula. Format as
    General and re- enter (F2, ENTER).

    4. Excel version 5.0a (yes, very, very old!) has serious recalculation bugs, Excel 97 absolutely needs Service Pack 2 (SP2)
    to calculate correctly.

    5. Very, very rarely, Excel's dependency tree gets messed up. One way to rebuild it is to find and replace all "=" by "=",
    for all sheets. Later versions of Excel rebuild by pressing CTRL+ALT+SHIFT+F9

    6. For many calculation secrets, visit Charles William's site:
    www.decisionmodels.com

    =========================================================


    --
    Kind regards,

    Niek Otten


    "DK" <[email protected]> wrote in message news:[email protected]...
    | Greetings and thank you in advance for any help.
    |
    | I have a workbook that is 671K in size and contains about 75 different
    | worksheets. The first tab is a summary and details are provided on the
    | remaining tabs.
    |
    | The problem I'm having is that the summary sheet has stopped updating. The
    | formulas are simple (e.g. sum a row, show the contents of a cell, etc.).
    | Calculation is set to automatic but I've tried switching it to manual and and
    | still nothing.
    |
    | The only way a cell will update is if I select a specific cell, F2 then ENTER.
    |
    | Any help you might provide is appreciated.



  3. #3
    Miguel Zapico
    Guest

    RE: Formulas no longer working

    One manual action that you may do to force the recalculation of formulas is
    open the replace tool (menu Edit->Replace), and then replace "=" with "="
    (just the equal sign, not the quotation marks).
    It looks something trivial, as you replace one thing for the same, but it
    may trigger the recalculation you need.

    Miguel.

    "DK" wrote:

    > Greetings and thank you in advance for any help.
    >
    > I have a workbook that is 671K in size and contains about 75 different
    > worksheets. The first tab is a summary and details are provided on the
    > remaining tabs.
    >
    > The problem I'm having is that the summary sheet has stopped updating. The
    > formulas are simple (e.g. sum a row, show the contents of a cell, etc.).
    > Calculation is set to automatic but I've tried switching it to manual and and
    > still nothing.
    >
    > The only way a cell will update is if I select a specific cell, F2 then ENTER.
    >
    > Any help you might provide is appreciated.


  4. #4
    DK
    Guest

    RE: Formulas no longer working

    Thanks to both of you for your suggestions.

    I tried the CTRL+ALT+SHIFT+F9 and that appears to have done the trick. I'm
    not sure why there was a problem as all my other workbooks continue to calc
    just fine but this one stopped.

    Thanks again.

    "DK" wrote:

    > Greetings and thank you in advance for any help.
    >
    > I have a workbook that is 671K in size and contains about 75 different
    > worksheets. The first tab is a summary and details are provided on the
    > remaining tabs.
    >
    > The problem I'm having is that the summary sheet has stopped updating. The
    > formulas are simple (e.g. sum a row, show the contents of a cell, etc.).
    > Calculation is set to automatic but I've tried switching it to manual and and
    > still nothing.
    >
    > The only way a cell will update is if I select a specific cell, F2 then ENTER.
    >
    > Any help you might provide is appreciated.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1