blank cell rather than 0

[puzzledpete]

i have some cells that refer to other cells

i.e

c4 =a1 until i enter something into a1 c4 gives a zero which inturn give other cells #N/A

so what formula do i enter into c4 to return a blank if nothing has yet been entered into a1 but will give the value of a1 when something has been entered?


thanks

[Special-K]

Format C4 as a Custom
and use type:

0;-0;;@

it will produce blank when there's nothing in A1 else it will show the value in A1

[martindwilson]

underlying value will still be zero even if you cant see it. what formula has problem with c4=0?

[puzzledpete]

i tried that and it gave me a blank cell but still gave me #N/A in the other cells

but i have solved it now with this

=IF(A1 =0,"",A1)


thanks anyway

[JBeaucaire]

If that's all you need, you can just turn of ZERO VALUE for the whole sheet. (this isn't global, it just applies to the visible sheet).

Go to TOOLS > OPTIONS > [ ] Zero Values and uncheck that. This is often better than all those extra IF evaluations.

[puzzledpete]

how do you do that in 2007 as there is not a tools tab?

[JBeaucaire]

Look here:
http://blogs.msdn.com/excel/archive/...22/603970.aspx

[martindwilson]

that will still leave 0 in cell but you cant see it.
try in a1 =b1/c1 where b1 has no value you still get #div/0 error
if however if c1 contains "" you get #value instead