# Thread: How do I eliminate a 0 in a target link

1. 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. By using an IF formula, e.g.

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

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

4. Yes, instead of simply using

=Sheet1!A1

in the target cell, now use

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

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

6. 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. 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. 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. 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. 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. 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. 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. Originally Posted by ababenchrist
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.

14. 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. 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
•