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

Topic

New Reply

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?

Reply | Quote

Replies

By using an IF formula, e.g.

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

Reply | Quote

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

Reply | Quote

Yes, instead of simply using

=Sheet1!A1

in the target cell, now use

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

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote