Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Referencing an empty cell in another worksheet elicits "0"

    Hi

    Not a big Excel user. I reference another cell:
    Code:
    =Sheet1!A1
    If the cell is populated, I get the value. If the cell is not populated I get "0". Any way to not get the "0" (ideally without using IF())?

    Chars
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58
    you could conditional format the cell, if the value is equal to zero then format the cell so the text is not shown.

    Under the options tab, zero values (unclick, but this does not help if you have a true 0 value you want to represent)

    And if of course which you don't want to use.

    I don't know of any other way than these 3 options. (unless of course you have vba script in the background that does it.)
    Or maybe I am forgetting the basics here?

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thanks for the ideas.
    It's all a bit "fudgy" isn't it? I'm always frustrated by Excel - it never quite seems to allow you to do things the easy and obvious way. I was (and still am) hoping that I am missing something basic, some option that won't screw up other zero values (I do have genuine ones too, as you suspected).

    Thanks again
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    It can certainly seem that way sometimes.


    Right click on the cell that contains the formula | format cells.

    On the number tab choose custom category and type this into the type textbox:

    0;-0;;@

    Hope that helps...

  5. #5
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58
    how slick is that, thanks colin L

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thank you Colin - I'll give it a go
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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