Linked cell returns a 0 where there is no text (sometimes)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ragtopcaddy - 26 Mar 2008 17:43 GMT

The contents of cell P10 on sheet("Issues") in T:\Storage\1.Reporting\DB\RAID
logs\RAID log.xls is empty.

The contenst of cell P8 on sheet("Issues") in T:\Storage\1.Reporting\DB\RAID
log.xls reads "='T:\Storage\1.Reporting\DB\RAID log\[RAID Log.xls]Issues'!
P10",
but is returning a value of 0. The contents of cells P4 and P5 in the source
wrkbk are also empty, but the links to those cells are also, correctly, empty.
It's only after 5 rows of correctly returned text that the 0s pop up. Any
idea what's happening here?

Signature

Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Reply to this Message

macropod - 27 Mar 2008 05:31 GMT

Hi Bill,

Any formula referencing an empty cell returns 0 from that cell. If you need to suppress the 0s, you'll need to either set the
worksheet to suppress the ) value display (which will affect all cells displaying 0) or add an IF test to the formula to test the
value and suppress it if the source cell is empty. For the latter:
=IF('T:\Storage\1.Reporting\DB\RAID log\[RAID Log.xls]Issues'!P10="","",'T:\Storage\1.Reporting\DB\RAID log\[RAID
Log.xls]Issues'!P10)

Cheers

Signature

macropod
[MVP - Microsoft Word]
-------------------------

> The contents of cell P10 on sheet("Issues") in T:\Storage\1.Reporting\DB\RAID
> logs\RAID log.xls is empty.
[quoted text clipped - 6 lines]
> It's only after 5 rows of correctly returned text that the 0s pop up. Any
> idea what's happening here?

Reply to this Message

Bill Manville - 27 Mar 2008 08:41 GMT

A cell that appears to be empty may not be.

There is a difference between a completely empty cell and a cell
containing a null string. Select the cells and take a look at the
formula bar. If you see ' in the formula bar then there is a null
string in the cell, or if you see a formula such as =IF(A1=0,"",A1) and
A1 is 0 then the cell will contain a null string.

A further complication is the option to suppress display of zero
values. In that case a cell might appear empty but actually contain 0.

A link to an empty cell or a cell containing 0 will always produce 0.
A link to a cell containing a null string will always produce a null
string.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message

ragtopcaddy - 28 Mar 2008 13:43 GMT

Thank you both for your suggestions. I will test them out.

>A cell that appears to be empty may not be.
>
[quoted text clipped - 14 lines]
>MVP - Microsoft Excel, Oxford, England
>No email replies please - respond to newsgroup

Signature

Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Reply to this Message

Rate this thread: