Formula returns "0"; How do I make it return nothing?

[jonlandrum]

It's a very basic formula: it simply returns data from another worksheet
in the workbook. However, if no data is entered in the cell a "0" will
be inserted in any cell that references it. I tried IF with a space for
value_if_true, like so:

=IF('sheet1!A1'=0, , 'sheet1!A1')

But that didn't work; it still returned "0". I then tried replacing the
space with a non-breaking space (Alt + 0160); that returned an error.
Any Ideas?

~Jonathan

 

[tony h]

try

=IF('sheet1!A1'=0,"" , 'sheet1!A1')

regards

 

[jonlandrum]

tony, it worked beautifully. Thank you very much! I don't know why I
didn't post that question here six months ago. I've just been coloring
the zeros white. )

Now I have to find another excuse for spending all day with these
files.

~Jonathan

 

[Bob Phillips]

You could also use a custom format of General;General;

and then just use

='sheet1!A1'

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[jonlandrum]

Bob, I didn't think of that, either. However, it wouldn't work because
the formating has to be either in date format or dollar format.

 

[Bob Phillips]

You can still use the technique

Assuming you never get negative dates

dd/mmm/yyyy;;

But with currency

£#,##0.00;-£#,##0.00;

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)