Forum Discussion
Solved
Reference to Sheet - Returning Blank if Cell is Blank #VALUE! Error
I'm trying to reference text cells from Sheet1 and insert blanks for blank references (as opposed to zeros). When using isblank() or len()=0, I receive #VALUE! errors for the longer text cells. ...
You could use:
=MAP(Sheet1!A1:A68,LAMBDA(x,IF(x=0,"",x)))The FILTER function can remove 0s but it would also contract the array. MAP will keep the numbers and blank out the 0s.
Another solution would be to custom format out the 0s. For example, custom formatting code:
0;-0;;General
You could use:
=MAP(Sheet1!A1:A68,LAMBDA(x,IF(x=0,"",x)))
The FILTER function can remove 0s but it would also contract the array. MAP will keep the numbers and blank out the 0s.
Another solution would be to custom format out the 0s. For example, custom formatting code:
0;-0;;General
Neat use of MAP! Not that I had realised that the original formula would fail. ISTEXT also works inside the Lambda function.