Formula Populating Cell With A Zero When It Should Be Empty

[Ark68]

.Range("C6:C17").Formula = "=INDEX('[" & wb_pbef.Name & "]CORE_DATA'!L2:$L$" & drows & ",Sandbox!$D$1)"

This line of code populates the range of cells C6:c17 with the index formula forom a second source workbook. The data in column L of the source data worksheet is normally text, but often times holds nothing. (cell is empty).

For those empty cells, this formula leave a value of 0 (zero) in the destination cell.

How can I ensure that the target cell remains empty, rather than being populated with a zero when the source cell is blank?

 

[kennypete]

Conceptually, the following works.

Book1
	A	B	C	D	E
1			3		42
2	0		2		0
3	42		1
Sheet1

Cell Formulas
Range	Formula
E1:E3	E1	=IF(INDEX($A$1:$A$3,C1,1)&""="","",INDEX($A$1:$A$3,C1,1))

It takes the INDEX result and adds "" to it so that it becomes a string, which, if empty returns "" whereas if 0 or any number will be false and then you can do the INDEX function. So, in the example above, E1 to E3 return the actual values, including E3, which is blank.

 

[kennypete]

Or, if you prefer:
=IF(ISBLANK(INDEX($A$1:$A$3,C3,1)),"",INDEX($A$1:$A$3,C3,1))
... will also do the trick.

 

[RoryA]

If the data is either text or blank, all you actually need to do to remove the 0 values is append an empty string:

.Range("C6:C17").Formula = "=INDEX('[" & wb_pbef.Name & "]CORE_DATA'!L2:$L$" & drows & ",Sandbox!$D$1)&"""""

 

[Ark68]

Thank you both. The support is most appreciated.