Jun 11 2021 06:13 AM
I'm having an issue trying to return the results of a filter without "0"s, because the value 0 actually means something in the data I am filtering.
To get around this I tried the following
=IF(ISBLANK(FILTER(DataSheet!$A$2:$V$5000,DataSheet1!$A$2:$A$5000=ThisSheet!$C$3)),"",FILTER(DataSheet!$A$2:$V$5000,DataSheet!$A$2:$A$5000=ThisSheet!$C$3))
But for any of the cells that have over 255 characters in DataSheet the above returns #Value! error
Any help for resolving the error or for displaying Filter results with 0 the character being kept but a null cell being represented by null or an empty string ""
Jun 11 2021 09:39 AM
No one formula in Excel could return blank value, thus ISBALNK(FILTER()) doesn't work, it's always FALSE. I guess the question is if C3 is blank. You may try
FILTER(DataSheet!$A$2:$V$5000,
(DataSheet!$A$2:$A$5000=ThisSheet!$C$3) *
(DataSheet!$A$2:$A$5000 <> "")
)
to exclude blanks from filtering. However, not sure which logic you'd kike to apply.
Jun 14 2021 01:58 AM
Jun 14 2021 04:04 AM
SolutionI see, that's interesting. I guess that's the limitation of any if() function - IF(), COUNTIF(), etc. They don't work with constants more than 255 characters, and in your case before array converted to range this limitation works.
Variant, if you work with texts, could be
=LET(str,FILTER(DataSheet!$A$2:$V$5000,DataSheet!$A$2:$A$5000=ThisSheet!$C$3), LEFT(str,1500) )
It there are numbers within the range they will be returned as texts.
Jun 14 2021 04:53 AM
so far i know (shouldn't mean much :) in all Formulas we can insert 1024 charakters.
Did I get something wrong? (which is the case now and then :)
Excel specifications and limits
Thx
Jun 14 2021 07:06 AM
Not exactly, we spear about variables, not about texts in grid. You may try
=IF(0, "a", "bbbbbbbbbb")
As soon as you repeat "b" in third parameter more than 255 times, you have an error
Practically is the same with COUNTIF() with long condition text, the only you have an error without explanations about contrition text length.
Same will be if simply ="bbbbb" if in above "b" is repeated more than 255 times. However,
=IF(0,A1,B1)
if in B1 is a long text, or
=IF(0, "a", REPT("b", 700) )
work.
Same with array formulas, before you convert array elements into grid range limit on variables will be applied.
Jun 14 2021 08:57 AM
Jun 14 2021 09:03 AM
Me too, I didn't think about this before
Jun 14 2021 09:09 AM
Jun 14 2021 01:33 PM
@Paul_Mountford , you are welcome and thank you for interesting case
Jun 14 2021 04:04 AM
SolutionI see, that's interesting. I guess that's the limitation of any if() function - IF(), COUNTIF(), etc. They don't work with constants more than 255 characters, and in your case before array converted to range this limitation works.
Variant, if you work with texts, could be
=LET(str,FILTER(DataSheet!$A$2:$V$5000,DataSheet!$A$2:$A$5000=ThisSheet!$C$3), LEFT(str,1500) )
It there are numbers within the range they will be returned as texts.