I'm trying, and failing, to modify the code in the Ignore Blanks in a Data Validation List in Excel post to work the way I need. I'm looking at the code in the "Complex Formula" section, the array formula:
=IFERROR(INDEX($A$2:$A$7,SMALL(IF(ISTEXT($A$2:$A$7),ROW($A$1:$A$6)), ROW(A1))),"")
https://www.teachexcel.com/excel-tutorial/ignore-blanks-in-a-data-validation-list-in-excel_1302.html
I can get the example in the post to work fine with some sample data, but in my case the cells containing the data aren't plan text, they're formulae based on other data in the sheet. As a result the ISTEXT function isn't appropriate to use as it always returns false.
Can anyone suggest an alternative formula for achieving this? Unfortunately I do need to support older versions of Excel so I can't use the Excel 365 code that is in the same post.
Edit - to make the issue a bit clearer...
My problem was that functions like ISTEXT or ISBLANK work on the formula in the cell, not on the result of the formula. I have a formula that sometimes return text and sometimes returns "", but the ISTEXT and ISBLANK function treat both cases the same...
ISTEXT = true
ISBLANK = false
My solution was to use LEN(...) > 0, I have posted it below.
Thanks.