Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Formulas appear but no values (Excel 97 SR2)

    Urgently need to resolve this:
    two columns
    each column contains formulas such as:
    "=[details.xls]Sheet1!$A$34"
    OR
    =[details.xls]Sheet1!sys230
    (where the sys230 is a named cell)
    OR
    =[details.xls]Sheet1!A34

    (there's a "square bracket"Details.xls"square bracket" immediately after the equals sign and before Sheet1! but when I post the square brackets and enclosed text disappears)
    All works well until you edit a cell, (and it can be as simple as going to edit but not changing anything), then it all turns to rat. The cell does not show the resultant value but the complete formula, centred within the cell irrespective of cell justification.
    Comparing the contents of two adjacent cells shows there is absolutely no difference in the cell contents, but the result is that one cell shows a value whereas the other shows the formula.
    Things I have tried:
    checked that Toolsoptionsviewformulas is not checked
    checked that ToolsoptionsCalculation is set to automatic
    checked that ToolsoptionsCalculation update remote references is checked
    selected F9 to recalc
    selected ctrl+alt+F9 to force calc

    Any suggestions??
    TIA
    Alan
    Cheshire
    UK

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Formulas appear but no values (Excel 97 SR2)

    Are these cells formatted as text? If so change them to general or a number format.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Formulas appear but no values (Excel 97 SR2)

    John, Thanks for suggestion. Cells are all formatted as numbers (and text where the result is a text string).
    Alan

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Formulas appear but no values (Excel 97 SR2)

    Hot off the press:
    Just got a solution and what a gotcha!
    MS actually has the solution but no fix to stop it happening in future, it's one of those, yeah we know it happens but when it does just use this cure:
    Open the worksheet that the error occurs in
    Select EditReplace
    Find what: =
    Replace with: =
    Select replace all
    and is if by magic, the formula disappears from general view and the values appear as one would expect under normal circumstances.
    I live and learn.
    I believe MS recognises this as a 'bug' for MAC edition Excel 98 but no mention of Excel 97.
    See MS Q184261
    Thanks again
    Alan

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas appear but no values (Excel 97 SR2)

    The formula was in the cell as text. That could happen be entering the formula while the cell was formatted as text. Changing the cell format to General or Number will not change the text to a formula without doing something like the find and replace that you did.
    Legare Coleman

Posting Permissions

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