Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
=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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Replace null string with blank cell | Excel Discussion (Misc queries) | |||
Cell color based upon cell value | Excel Discussion (Misc queries) | |||
cell color index comparison | New Users to Excel | |||
up to 7 functions? | Excel Worksheet Functions | |||
Identify if a cell is referenced by other cells | Excel Worksheet Functions |