So I am trying to implement data validation in Excel where the data comes from a dynamic list. Since I don't know the length of the list, I want to use data validation to make a drop down list including all of the text in a given column. I have a table and a filter function to return those rows from the table that meet a criteria. I want to use the columns from the filter function for data validation. For example, I have some data in column J that I want to be able to choose from in B20. My solution was to allow: list, source: =J:J. The problem I am having is that this gives me lots of blank rows in the drop down - apparently the number of rows in the entire table, but I only want the rows that actually have text in them. I've tried several variations of allow:custom, source: =ISTEXT(J1), but this doesn't work. How can I get rid of the blank rows?
Bookmarks