Forum Discussion
#VALUE! error when referencing a range of cells
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
- Everything in Sheet1 is coming in from an external data source.
Could the blank-looking cells contain one or more spaces?
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&""
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.
