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.

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.

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.

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

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.

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.

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

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.

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