Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Ignore Blanks in a Data Validation List in Excel

0

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. 

Answer
Discuss

Discussion

FYI ISBLANK does not recognize the empty text string ("") as being blank. You need to use the COUNTBLANK function which does recognize the null text string
pecoflyer Nov 22, '21 at 3:02 am
Add to Discussion

0

Hello gavinallan50

Looking at recent unanswered post I noticed yours.

I modified your file to use a macro to update the data validation list.

The macro deletes the existing DV list (column "N"), copies source data entries - no blanks - in Column "A", then pastes these into column "N". I added a rounded rectangle shape and assigned the macro to it. Just click the 'button' and the list in Column "N" is updated to the entries in column "A". This updated list is then used as DV for "Q2".

Cheers.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login