Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I’m using an Excel 2007 workbook. That has links to different sheets within workbook. How do I eliminate a “0” in a target link if no data was entered from source?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    By using an IF formula, e.g.

    =IF(OtherSheet!F37="","",OtherSheet!F37)


  3. #3
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi HansV
    Thank you
    These is new territory for me, Do I insert the formula at target location or?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Yes, instead of simply using

    =Sheet1!A1

    in the target cell, now use

    =IF(Sheet1!A1="","",Sheet1!A1)


  5. #5
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi HansV
    Oh thats easy, Thank you so much for explaining.

  6. #6
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi HansV
    my target cell is still showing a "0"
    My target cell formula was
    =Client_info!$N$64
    Then I changed it to
    =IF(Client_info!$N$64="","",Client_info!$N$64)

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try:

    =IF(Client_info!$N$64=0,"",Client_info!$N$64)

    If this does not work, what is the result of Client_info!$N$64

  8. #8
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Nathan
    That appears to have rectified my problem
    will this also work with formula's containing $
    or will I need to include this $ with formula

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The result will be the same, both with and without the $. The $ just makes the cell reference absolute, but has no impact on the result.

    Whether you need the $ or not, depends on whether you require absolute or relative formulas.

  10. #10
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Nathan
    now that I have removed "0" from target cells, I am recieving a #VALUE! form a formulated cell that uses for calculation

    =SUM(A24*AP24)

    do I use the sam method

  11. #11
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I assume that either A24 and/or AP24 contains "".

    AFAIK, you cannot calculate a range that contains "" text values.

    Another approach which may better suit your needs is to use conditional formatting to make the zero results invisible, well not invisible, but the same font colour as the cell background. This would be instead of the If statements presented earlier.

    Post back if you need further guidance.

  12. #12
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Nathan
    That’s what I will do then,
    In cells A24 and AP24 and target cell (AW24) I’ll use conditional formatting that will display “0” with same color as fill color for that cell, this will give the illusion of invisibility. I like it thank you.

  13. #13
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by ababenchrist View Post
    Thank you Nathan
    That’s what I will do then,
    In cells A24 and AP24 and target cell (AW24) I’ll use conditional formatting that will display “0” with same color as fill color for that cell, this will give the illusion of invisibility. I like it thank you.
    An alternative (doing away with the need to apply conditional formatting to each relevant cell) is to change the setting for the whole sheet to not show zero values.

    Click the Office Button/Excel Options/Advanced. Scroll down to the 'Display options for this worksheet' section and de-select the 'Show a zero in cells that have a zero value'. This setting only applies to the active worksheet.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  14. #14
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Tony
    That would have been a much easier approach, but I have already put into place conditional formatting in needed cells. I thank you though and shall put this info away for future use.

  15. #15
    New Lounger
    Join Date
    Dec 2009
    Location
    Central Texas, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Another way is to change the format. Right-click on a cell, Format Cells, Number tab, Custom. enter this:

    General;General;;@

    Note the space between the 2nd and 3rd semicolon. The first item is for positive numbers, the second is for negative numbers, the third is zero (we're indicating to show nothing for zeroes), and the 4th is non-numeric items. In this case, we want nothing if zero. Custom number formats can do some interesting things.

    With all of the solutions provided, you will have a problem if you really do have a zero to display. The workaround for that would be to create a user-defined function or macro. With a UDF, you could analyze the origin cell to check for sure whether it's really a zero, and if so, display a zero in the target cell. With a macro, you could have it activate automatically whenever a change is made, and make the same adjustment if needed, without having to change any formulas in the cells.

    --Scott.

Posting Permissions

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