Results 1 to 5 of 5
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Excel formula does not show values if formula edited

    This was a tough one to find a title for but .......

    Anyone encountered this on Excel 2007 running SP2

    If a formula is copied and pasted into another cell then it will calculate and show the answer
    BUT...
    If you type a formula into a cell OR Paste a formula into the formula bar, then it just shows as
    as formula with the = at the front.

    I have heard that this is a bug, and that it can be fixed by formatting the cell as Text.
    BUT.. This solution does not work, it still says as a formula.

    AND ... In case anyone asks, it is not set to show formulas, it only happens on some cells.
    If you copy a working formula from another cell, and paste it into same cell, it is OK.
    If you take the same formula and paste it into the formula bar, it shows as a formula.

    Is there something to stop this happening, or is it just a BUG (surely not) in Excel.
    Andrew

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Andrew,

    Could you please post an example workbook where this is happening?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Probably not.

    It is not my data, and is highly confidential anyway.
    AND.. I am not sure I will be allowed a copy to post even if sanitised.
    If I can I will upload it.
    I am also going to see if it still ocurs in 2010, but I cannot do that until Thu.
    Andrew

  4. #4
    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
    Formatting the cell as Text causes that problem, rather than curing it.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Thinks! & thanks

    Quote Originally Posted by rory View Post
    Formatting the cell as Text causes that problem, rather than curing it.
    Thanks Rory.

    Interesting, I was just going on hearsay on that one.
    That is what they told me, I had not encountered the issue.

    I do know that the data for the main bulk is imported,
    but the columns that are showing the issue are ones that have been inserted after the import.
    I just wonder if they got the wrong end of the stick regarding text.

    Infact, thinking ??????????????????????? about it,
    I reckon the cells may have been set as text format after the import.
    Then new columns inserted are getting the same text format as adjacent ones.
    If you change the format back to General it does not change it back to an answer unless you edit the formula.

    Hmmmmm.

    I will let you know the outcome shortly ........
    And I have just tested that theory and VOILA!

    I probably should have looked at the sheet in more detail, but they wouldn't let me play with it!
    AND I was a tad preoccupied at the time with some complex vba coding.

    Doh3.jpg

    Many Thanks for a nudge in the right direction!
    Last edited by AndrewKKWalker; 2011-10-31 at 16:12.
    Andrew

Posting Permissions

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