#VALUE! error when referencing a range of cells

Copper Contributor

I'm getting a #VALUE! error in only certain cells when using the formula =IF(ISBLANK('Sheet1'!N2:N22), "", 'Sheet1'!N2:N22). A couple things to note, changing the formula to ='Sheet1'!N2:N22 gives the range with no cells giving the #VALUE! error, however the original empty cells are now "0". Also, using the formula =IF(ISBLANK('Sheet1'!N4), "", 'Sheet1'!N4) does not give an error, but when I change the reference to include a range such as =IF(ISBLANK('Sheet1'!N4:N5), "", 'Sheet1'!N4:N5) it gives the #VALUE! error in the top cell (even though it didn't give an error when referencing the single cell). 

4 Replies

@Sconk 

Does this work?

 

=IF('Sheet1'!N2:N22="", "", 'Sheet1'!N2:N22)

 

If all cells in N2:N22 are either blank or text, you can use a shorter formula

 

='Sheet1'!N2:N22&""

@Hans Vogelaar 

That still gives the same result. It has something to do with the text being referenced because it works for some, but not for all. 

Everything in Sheet1 is coming in from an external data source.

@Sconk 

Could the blank-looking cells contain one or more spaces?