blank cell rather than 0

**puzzledpete** · 04-24-2009, 06:15 PM

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** · 04-24-2009, 06:35 PM

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** · 04-24-2009, 07:04 PM

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

**puzzledpete** · 04-24-2009, 07:10 PM

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** · 04-25-2009, 12:06 AM

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** · 04-25-2009, 05:54 AM

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

**JBeaucaire** · 04-25-2009, 10:35 AM

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

**martindwilson** · 04-25-2009, 12:16 PM

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