• How do I eliminate a “0” in a target link

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » How do I eliminate a “0” in a target link

    Author
    Topic
    #463702

    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?

    Viewing 4 reply threads
    Author
    Replies
    • #1184742

      By using an IF formula, e.g.

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

      • #1184744

        Hi HansV
        Thank you
        These is new territory for me, Do I insert the formula at target location or?

        • #1184745

          Yes, instead of simply using

          =Sheet1!A1

          in the target cell, now use

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

          • #1184746

            Hi HansV
            Oh thats easy, Thank you so much for explaining.

          • #1184748

            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)

    • #1184749

      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

      • #1184755

        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

    • #1184757

      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.

      • #1184760

        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

    • #1184761

      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.

      • #1184763

        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.

        • #1184892

          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.

          • #1184918

            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.

    • #1189953

      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.

    Viewing 4 reply threads
    Reply To: How do I eliminate a “0” in a target link

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: