Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Why does linked data from blank cells become a zero?
When linking data within the same workbook (to different tabs) any cell that
is empty - shows up in the new tab as a ZERO (0).....what am I doing wrong? I want those cells to remain blank. |
#2
|
|||
|
|||
That's just the way it is ..
Instead of : =Sheet2!A1 you could use an error trap: =IF(Sheet2!A1="","",Sheet2!A1) Alternatively, we could suppress the display of extraneous zeros in the entire sheet via: Click Tools Options View tab Uncheck "Zero values" OK -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "MJ" wrote in message ... When linking data within the same workbook (to different tabs) any cell that is empty - shows up in the new tab as a ZERO (0).....what am I doing wrong? I want those cells to remain blank. |
#3
|
|||
|
|||
If you enter formula "=A2" and A2 is blank, Excel evaluates it as zero.
One way to get around this is to use =if(A2=0,"",A2). This will make the cell appear to be blank, but will make some functions return an error. If text creates an error in functions you are using, but a zero would not distort the calculation (using addition and subtraction, but not averages, etc.) you can leave formulas as you have them and go to tools-options, select the "View" tab and uncheck "zero values" under window options near the bottom. "MJ" wrote: When linking data within the same workbook (to different tabs) any cell that is empty - shows up in the new tab as a ZERO (0).....what am I doing wrong? I want those cells to remain blank. |
#4
|
|||
|
|||
But this formula:
=if(A2=0,"",A2) will also return "" if A2 actually contains 0 (not empty). I'd use Max's suggestion and check for "" =if(a2="","",a2) eider wrote: If you enter formula "=A2" and A2 is blank, Excel evaluates it as zero. One way to get around this is to use =if(A2=0,"",A2). This will make the cell appear to be blank, but will make some functions return an error. If text creates an error in functions you are using, but a zero would not distort the calculation (using addition and subtraction, but not averages, etc.) you can leave formulas as you have them and go to tools-options, select the "View" tab and uncheck "zero values" under window options near the bottom. "MJ" wrote: When linking data within the same workbook (to different tabs) any cell that is empty - shows up in the new tab as a ZERO (0).....what am I doing wrong? I want those cells to remain blank. -- Dave Peterson |
#5
|
|||
|
|||
MJ
You are doing nothing wrong. Excel does that for you. Either turn off zeros through ToolsOptionsView or......... Enter a formula in your linked cells that change the values to blank. =IF(Sheet1!A1="","",Sheet1!A1) Gord Dibben Excel MVP On Thu, 28 Jul 2005 15:30:02 -0700, "MJ" wrote: When linking data within the same workbook (to different tabs) any cell that is empty - shows up in the new tab as a ZERO (0).....what am I doing wrong? I want those cells to remain blank. |
#6
|
|||
|
|||
Max wrote:
you could use an error trap: =IF(Sheet2!A1="","",Sheet2!A1) But bewa the cell with this IF() function will no longer be treated as zero if it is blank. For example, if A:A (the entire column) has some blank cells mixed with values, and B:B has =A1 etc, and C:C has =5*B1 etc, then if you change B:B to =IF(A1="","",A1) etc as suggested above, you must also change C:C to =IF(B1="","",5*B1) etc. In other words, once you start propagating blank cells in the manner suggested, you lose the ability for apparently "blank" cells to be treated as zero. Apparently Excel treats only truly empty cells as blank, not cells with the null string "". Even though the test A1="" matches both the null string and truly empty cells, apparently the null string "" is not the same as an empty cell :-(. At least, that has been my experience with Office Excel 2003. If there is an option to treat the null string as an empty cell -- or to treat any string as zero in an arithmetic expression -- I would like to hear about it. PS: I coulda sworn that some previous verions of Excel did indeed treat a cell with only strings as zero when they are referenced in an arithmetic expression. But perhaps my memory is wrong. |
#7
|
|||
|
|||
... But bewa the cell with this IF() function will no longer be treated as zero if it is blank. Yes, of course. And this may affect downstream calculations reading these cells as you rightly pointed out. OTOH, we could also do it as: =IF(Sheet2!A1="",0,Sheet2!A1), and then switch off the zeros display in the sheet <g. To achieve a cleaner view, I'd usually just go for the option of switching off the zero values display in the sheet, and leave the link formula as it stands, w/o the error trap. If there is an option to treat the null string as an empty cell -- or to treat any string as zero in an arithmetic expression -- I would like to hear about it. An example could be to use SUM() to avert downstream arithmetic errors, since SUM seems to treat null strings/text as zeros: Using : =SUM(A1:B1) instead of: =A1+B1 Using : =SUM(B1)-SUM(A1) instead of: =B1-A1 Believe the 1st expression is quite commonly used, while the 2nd is probably not (it's just an example) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#9
|
|||
|
|||
But if sheet1!a1 were numeric, =clean() just changed it to text.
(Even worse looking if sheet1!a1 held a date.) Sanjeev wrote: Dear MJ, May be You can use this also it was simple & more easy =CLEAN(Sheet1!A1) Sanjeev Agarwal "Gord Dibben" wrote: MJ You are doing nothing wrong. Excel does that for you. Either turn off zeros through ToolsOptionsView or......... Enter a formula in your linked cells that change the values to blank. =IF(Sheet1!A1="","",Sheet1!A1) Gord Dibben Excel MVP On Thu, 28 Jul 2005 15:30:02 -0700, "MJ" wrote: When linking data within the same workbook (to different tabs) any cell that is empty - shows up in the new tab as a ZERO (0).....what am I doing wrong? I want those cells to remain blank. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Skipping cells while reorganizing data | Excel Discussion (Misc queries) | |||
Autofill data in specific blank cells | Excel Worksheet Functions | |||
Help making a chart that doesn't graph cells without data? | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
How do I copy data (word) into respective cells when the data bei. | New Users to Excel |