Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
dee
 
Posts: n/a
Default 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!!!
  #2   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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 ,
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!!!

  #3   Report Post  
Duke Carey
 
Posts: n/a
Default

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!!!

  #4   Report Post  
dee
 
Posts: n/a
Default

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!!!

  #5   Report Post  
dee
 
Posts: n/a
Default

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 ,
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!!!




  #6   Report Post  
Duke Carey
 
Posts: n/a
Default

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!!!

  #7   Report Post  
JE McGimpsey
 
Posts: n/a
Default

In article ,
dee 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.
  #8   Report Post  
dee
 
Posts: n/a
Default

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!!!

  #9   Report Post  
Gary''s Student
 
Posts: n/a
Default

=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!!!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Replace null string with blank cell gjcase Excel Discussion (Misc queries) 2 August 9th 05 03:13 PM
Cell color based upon cell value My View Excel Discussion (Misc queries) 11 July 6th 05 04:59 AM
cell color index comparison MINAL ZUNKE New Users to Excel 1 June 30th 05 08:11 AM
up to 7 functions? ALex Excel Worksheet Functions 10 April 12th 05 07:42 PM
Identify if a cell is referenced by other cells drhayes Excel Worksheet Functions 4 February 25th 05 03:12 PM


All times are GMT +1. The time now is 05:10 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"