cell shows 0 when referenced cell is null

Hi,
I have a cell in worksheet2 which references a cell in worksheet 1:
e.g = Worksheet1!A3
But if A3 in woksheet 1 is empty (null?) the cell in worksheet2 has a 0 in it.
Is there any way to get the cell in worksheet2 to also show null?
i need it to show null and not "" because of conditonal formatting I have
thanks!!!

A formula can't return a null/empty result.

You can, of course, use

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

I'm not sure why you can't use a null string ("") with Conditional
Formatting...

In article <[email protected]>,
dee <[email protected]> wrote:

> Hi,
> I have a cell in worksheet2 which references a cell in worksheet 1:
> e.g = Worksheet1!A3
> But if A3 in woksheet 1 is empty (null?) the cell in worksheet2 has a 0 in it.
> Is there any way to get the cell in worksheet2 to also show null?
> i need it to show null and not "" because of conditonal formatting I have
> thanks!!!

The value and the display are 2 different things. If it references an empty
cell its VALUE is zero. You can format the cell to not display the zero,
though it will still have a zero value. The other option - to show "" -
you've already dismissed.

Maybe your best bet is to adjust your conditional formatting logic

"dee" wrote:

> Hi,
> I have a cell in worksheet2 which references a cell in worksheet 1:
> e.g = Worksheet1!A3
> But if A3 in woksheet 1 is empty (null?) the cell in worksheet2 has a 0 in it.
> Is there any way to get the cell in worksheet2 to also show null?
> i need it to show null and not "" because of conditonal formatting I have
> thanks!!!

hi duke,
the original cell in worksheet one does not have formatting not to display
the value zero. both worksheet1 and worksheet2 just have general formatting
set on the cells. It doesnt make snes why one shows nothing and the other
zero, any ideas??
thanks!

"Duke Carey" wrote:

> The value and the display are 2 different things. If it references an empty
> cell its VALUE is zero. You can format the cell to not display the zero,
> though it will still have a zero value. The other option - to show "" -
> you've already dismissed.
>
> Maybe your best bet is to adjust your conditional formatting logic
>
> "dee" wrote:
>
> > Hi,
> > I have a cell in worksheet2 which references a cell in worksheet 1:
> > e.g = Worksheet1!A3
> > But if A3 in woksheet 1 is empty (null?) the cell in worksheet2 has a 0 in it.
> > Is there any way to get the cell in worksheet2 to also show null?
> > i need it to show null and not "" because of conditonal formatting I have
> > thanks!!!

Hi JE,

this is exactly what I cant do, when I set the cell on worksheet2 to "" it
mucks up the conditional formatting, turning the cell green when it should be
white (ie no conditional formatting because its a null cell)
Im really confused as to why the cell appears empty on 1 sheet and 0 on the
other, when there is no apparent differences in the display format 9both set
to general)
any ideas?
thanks!

"JE McGimpsey" wrote:

> A formula can't return a null/empty result.
>
> You can, of course, use
>
> =IF(Sheet1!A1="","",Sheet1!A1)
>
> I'm not sure why you can't use a null string ("") with Conditional
> Formatting...
>
> In article <[email protected]>,
> dee <[email protected]> wrote:
>
> > Hi,
> > I have a cell in worksheet2 which references a cell in worksheet 1:
> > e.g = Worksheet1!A3
> > But if A3 in woksheet 1 is empty (null?) the cell in worksheet2 has a 0 in it.
> > Is there any way to get the cell in worksheet2 to also show null?
> > i need it to show null and not "" because of conditonal formatting I have
> > thanks!!!
>

You're missing my (and JE's) point: a cell that references an empty cell HAS
A VALUE OF ZERO. It's not empty, it's not blank, it's zero. The mere act of
referencing the empty cell changes the current cell's status.

I think you simply need to change your conditional formatting logic!! Have
it deal with the empty string.


"dee" wrote:

> hi duke,
> the original cell in worksheet one does not have formatting not to display
> the value zero. both worksheet1 and worksheet2 just have general formatting
> set on the cells. It doesnt make snes why one shows nothing and the other
> zero, any ideas??
> thanks!
>
> "Duke Carey" wrote:
>
> > The value and the display are 2 different things. If it references an empty
> > cell its VALUE is zero. You can format the cell to not display the zero,
> > though it will still have a zero value. The other option - to show "" -
> > you've already dismissed.
> >
> > Maybe your best bet is to adjust your conditional formatting logic
> >
> > "dee" wrote:
> >
> > > Hi,
> > > I have a cell in worksheet2 which references a cell in worksheet 1:
> > > e.g = Worksheet1!A3
> > > But if A3 in woksheet 1 is empty (null?) the cell in worksheet2 has a 0 in it.
> > > Is there any way to get the cell in worksheet2 to also show null?
> > > i need it to show null and not "" because of conditonal formatting I have
> > > thanks!!!

In article <[email protected]>,
dee <[email protected]> wrote:

> Im really confused as to why the cell appears empty on 1 sheet and 0 on the
> other, when there is no apparent differences in the display format 9both set
> to general)

Formulas return values. If a cell is empty, the empty value that is
returned will be coerced to either zero or the null string, depending on
the context.

Display format has nothing to do with the value returned/stored in the
cell.

> any ideas?

Without seeing what you're using for CF, no.

aaah! i get it thanks!!!!


"Duke Carey" wrote:

> You're missing my (and JE's) point: a cell that references an empty cell HAS
> A VALUE OF ZERO. It's not empty, it's not blank, it's zero. The mere act of
> referencing the empty cell changes the current cell's status.
>
> I think you simply need to change your conditional formatting logic!! Have
> it deal with the empty string.
>
>
> "dee" wrote:
>
> > hi duke,
> > the original cell in worksheet one does not have formatting not to display
> > the value zero. both worksheet1 and worksheet2 just have general formatting
> > set on the cells. It doesnt make snes why one shows nothing and the other
> > zero, any ideas??
> > thanks!
> >
> > "Duke Carey" wrote:
> >
> > > The value and the display are 2 different things. If it references an empty
> > > cell its VALUE is zero. You can format the cell to not display the zero,
> > > though it will still have a zero value. The other option - to show "" -
> > > you've already dismissed.
> > >
> > > Maybe your best bet is to adjust your conditional formatting logic
> > >
> > > "dee" wrote:
> > >
> > > > Hi,
> > > > I have a cell in worksheet2 which references a cell in worksheet 1:
> > > > e.g = Worksheet1!A3
> > > > But if A3 in woksheet 1 is empty (null?) the cell in worksheet2 has a 0 in it.
> > > > Is there any way to get the cell in worksheet2 to also show null?
> > > > i need it to show null and not "" because of conditonal formatting I have
> > > > thanks!!!

=IF(Sheet1!$A$3="","",Sheet1!$A$3)
--
Gary''s Student


"dee" wrote:

> Hi,
> I have a cell in worksheet2 which references a cell in worksheet 1:
> e.g = Worksheet1!A3
> But if A3 in woksheet 1 is empty (null?) the cell in worksheet2 has a 0 in it.
> Is there any way to get the cell in worksheet2 to also show null?
> i need it to show null and not "" because of conditonal formatting I have
> thanks!!!