Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    New Lounger
    Join Date
    May 2016
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Cell does not display formula results

    Thank you in advance for helping if you're able. I entered a formula in an Excel cell, and the cell is blank. It's supposed to deliver content from another cell (text, not numbers, if you're wondering). I've tried the obvious things black text in white cell, formatted cell as General (tried text too), and still nothing. Here's where it gets funky. I KNOW the formula is correct, because when I go to Format Cell > Number, the answer I want to display shows up in the sample box! So it's calculating correctly, it just won't show in the cell. Help?

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Andeev,

    If your formula evaluates to zero, is it possible that you have Excel set NOT to display zero values with a zero? It will display a blank cell instead.

    In Excel 2007+ goto File > Options > Advanced > "Display Options for This Workbook" section > tick "Show a zero in cells that have a zero value"

    Untitled.png

    HTH,
    Maud

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Andeev

    To solve your problem, Ctrl-C to copy any cell (that actually shows a result),
    ..then, on the problem cell, use PasteSpecial>Formats

    The reason it doesn't display anything is probably because that cell is formatted to 'hide' all contents.
    To 'hide' a cell's content, you just assign a custom format of ;;;
    ..that's three semi-colons i.e. ;;;
    (NOTE: using this custom format ;;; also prevents that cell from being printed, and it won't appear on print>preview either)

    zeddy
    Last edited by zeddy; 2016-05-16 at 04:53.

  4. The Following User Says Thank You to zeddy For This Useful Post:

    RetiredGeek (2016-05-16)

  5. #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
    Based on all that, I'd suspect conditional formatting is hiding the results.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi rory

    ..your suspect is more likely than my probably

    zeddy

  7. #6
    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
    It's possible. Maybe.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #7
    Star Lounger
    Join Date
    May 2016
    Posts
    50
    Thanks
    9
    Thanked 4 Times in 4 Posts
    Zeddy,

    can i use that 3 semi-colon format trick in conditional formatting ?

    Reason i ask is because i'm currently working on highlight rows on mouse over code, and the workbook i'm using does have some "N/A" returns from formulas, which i've formatted to white font, but obviously as i mouse over they show up, so i have to format them again to match the highlight colour, just curious if i can use this trick to hide the N/A value completely and save myself a conditional formatting step.

    thanks

  9. #8
    New Lounger
    Join Date
    May 2016
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi all,
    Thanks for your responses. Turns out the problem has nothing to do with formulas, it's a problem of the data in that cell simply not displaying. I've even tried to type random text in it. The text shows in the formula bar, but will NOT display in the cell! The cell is transparent, type is black, it's unlocked and unhidden.

    Sincerely,
    Ready To Pull My Hair Out
    Screen Shot 2016-05-17 at 9.24.41 AM.png

  10. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Attaching a workbook sample is long overdue...otherwise, everyone is making their best intelligent guesses at this.

  11. #10
    New Lounger
    Join Date
    May 2016
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    @kweaver I understand, unfortunately it's a spreadsheet with a lot of proprietary information that doesn't belong to me. Just helping a friend out who has no knowledge of Excel at all. The screen grab I posted above – it shows that the content is there in the function box, but not in the cell. Is there something else I can share that might help, short of uploading the whole workbook? This is my first time on here, so I'm a novice at this. Thanks for your patience.

  12. #11
    Star Lounger
    Join Date
    May 2016
    Posts
    50
    Thanks
    9
    Thanked 4 Times in 4 Posts
    highlight a couple of cells below and to the side, then cross your problem cell, and see if the info is then visible, that'll give some insight as to whether it's a formatting issue or something else, one thing i have seen cause similar effects in the past is a long forgotten about lbel or text box that is over the top of the cell, and set to opaque

  13. The Following User Says Thank You to Gunslinger For This Useful Post:

    Andeev (2016-05-17)

  14. #12
    New Lounger
    Join Date
    May 2016
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Screen Shot 2016-05-17 at 12.17.27 PM.png
    Quote Originally Posted by Gunslinger View Post
    highlight a couple of cells below and to the side, then cross your problem cell, and see if the info is then visible, that'll give some insight as to whether it's a formatting issue or something else, one thing i have seen cause similar effects in the past is a long forgotten about lbel or text box that is over the top of the cell, and set to opaque
    Gunslinger, you're right! When I highlight a group of cells, you can finally see the text inside. See attachment. How do I fix this?

    Thanks again!
    Last edited by Andeev; 2016-05-17 at 13:21.

  15. #13
    New Lounger
    Join Date
    May 2016
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Gunslinger, I figured out the rest once you helped identify the root of the problem. I really appreciate your help!

    I selected the cell, the did Edit > Clear > Formats.

    Thanks again!

  16. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Andeev

    As per post#3, did you try to Ctrl-C copy an existing OK cell, and then pasteSpecial>formats to the problem cell???
    ..just curious.

    zeddy

  17. #15
    New Lounger
    Join Date
    May 2016
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Zeddy,
    I admit I got lost in the lingo of the ;;;. I couldn't figure out where to put the 3 semicolons, and the previous suggestion(s) had to do with formulas, which I didn't believe was the problem. Now that I have a handle on where the problem existed, I retraced back to your suggestion and I believe that absolutely would have fixed it. I should have admitted then that the semicolons were over my head. I thought it had to do with the conditional formatting that someone else mentioned, which I spent so much time exploring that I lost time off my life.

    Thanks for your help, I really do appreciate it.

Page 1 of 2 12 LastLast

Posting Permissions

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