Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    I have a workbook with a summary worksheet that references cells in other worksheets.

    This is in Office XP.
    Occassionally a cell will show the formula instead of the result.


    I can copy the formula from another cell and it works fine until I edit the reference.

    eg
    =IF('S14'!H2=0,"",'S14'!H2)
    same result if I remove the If statement or just type '=' then goto the other sheet and click on the cell.

    Any ideas?

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,202
    Thanks
    49
    Thanked 987 Times in 917 Posts
    Showing just the formula can occur if you put an apostrophe as the first character. Doesn't show unless you edit the cell.

    cheers, Paul

  3. #3
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by P T View Post
    if you put an apostrophe as the first character
    It converts the inputs in to plain text. can viewed in formula bar.

    in addition, holding down ~with Ctrl key shows formula itself instead of results and vice versa.
    Regards
    Prasad

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Paul - The formula shows even if it doesn't start with an apostophe. Even entering a simple formula like - =if(12=0,0,12) has the same problem.

    Hi Prasad - the Tilde + Ctrl shows the formula/result for all cells. In this case it is just the odd one or two that show the formula as plain text while all the others just show the result.

    I can't find any way to toggle from plain text / show result for a single cell that may be affecting the offending cells.

    If I view Evaluate Formula the reference to the cell in the other sheet is underlined for cells that correctly display the result but for the offending cells Evaluate Formula seems to believe that the cell contains a constant.

    So it seems that the problem is that when I edit the formula Excel no longer sees the reference as a valid reference even if the edit does not change the reference.

    I've tested to make sure that it's not an issue with the cell in the other sheet being referenced that is the problem.

    So any ideas as to why the reference becomes invalid when I edit the formula?

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    I found a work around.

    If I change the cell format from text to number BEFORE I enter the formula it displays OK.

    If I change the format to number for a cell which is currently displaying the formula instead of the result then it does not fix it.

    So I have to delete the fomula, change the format to number, then re-enter the formula.

    Problem solved.

  6. #6
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by kentg View Post
    I have a workbook with a summary worksheet that references cells in other worksheets.

    This is in Office XP.
    Occassionally a cell will show the formula instead of the result.


    I can copy the formula from another cell and it works fine until I edit the reference.

    eg
    =IF('S14'!H2=0,"",'S14'!H2)
    same result if I remove the If statement or just type '=' then goto the other sheet and click on the cell.

    Any ideas?
    This question arises fairly often, and is discussed all over the Web. The consensus, which is supported by my personal experience, is that the most reliable way to fix this problem is to set the format of the troublesome cell to General.

    This seems counter-intuitive, because this condition seems to affect cells that contain formulas which return text strings. Nevertheless, this solution works because General format displays text correctly and reliably.

    In contrast, there is some weird, but more or less predictable, interaction between formulas that return text strings and a cell format type of Text. Go figure, and let us all know if you figure it out.
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FYI, you don't have to delete the formula, change the format, then re-enter it. Simply change the format, then press f2 and enter to re-enter the formula.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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