Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links
|
|||
|
|||
Linked cell returns a 0 where there is no text (sometimes)
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? -- Bill Reed "If you can't laugh at yourself, laugh at somebody else" Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.links
|
|||
|
|||
Linked cell returns a 0 where there is no text (sometimes)
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 -- macropod [MVP - Microsoft Word] ------------------------- "ragtopcaddy via OfficeKB.com" <u9289@uwe wrote in message news:81bdabf357e11@uwe... 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? -- Bill Reed "If you can't laugh at yourself, laugh at somebody else" Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.links
|
|||
|
|||
Linked cell returns a 0 where there is no text (sometimes)
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 |
#4
Posted to microsoft.public.excel.links
|
|||
|
|||
Linked cell returns a 0 where there is no text (sometimes)
Thank you both for your suggestions. I will test them out.
Bill Manville wrote: 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 -- Bill Reed "If you can't laugh at yourself, laugh at somebody else" Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy text from word with returns into one cell | Excel Discussion (Misc queries) | |||
IF function which returns the text from a cell | Excel Worksheet Functions | |||
How can I enter carriage returns into text within a cell in excel | Excel Discussion (Misc queries) | |||
linked objects following and leading returns | Excel Discussion (Misc queries) | |||
linked objects following and leading returns | Excel Worksheet Functions |