Set Data Validation list to ignore blanks in source list

    I have a workbook with (3) tabs. Tab #1 contains a column with a large list of dynamic data. Tab #2 contains a drop down List made through "Data Validation". Tab #3 has the same number of rows as Tab#1 and looks at each row for a text value and returns a text value if it exists otherwise it returns a blank value. The List on Tab#2 references the list on Tab #1.


    The problem I'm having is that when I make the drop down list on Tab#2, it shows all of the blanks for all the cells on Tab#3 that returned blank values. Is there a way to have the list only show values return a text value even though there is a formula in each cell that returns a blank value?


    I've tried setting up a dynamic range but it doesn't seem to work.

    Re: Set Data Validation list to ignore blanks in source list


    To get the most precise answers, it is best to upload a sample workbook (sensitive data scrubbed/removed) that shows a few manually created examples of the desired results.
    The structure and data types of the workbook must exactly match that of the real workbook. Include in the workbook a clear and explicit explanation of all requirements.The sample workbook only needs to contain enough data to illustrate the need to aid with developing the solution.


    [COLOR="navy"]How to edit your post or thread title or upload an attachment[/COLOR]


    1. Click the EDIT POST in the gray band immediately below your post
    2. Click Go Advanced
    3. Edit the post or thread title
    4. To upload: scroll down to Manage Attachments

    Re: Set Data Validation list to ignore blanks in source list


    Hi Juger,
    Nice to meet you.
    I use array formula and named range.
    =IF(ISERROR(SMALL(IF(ISNUMBER(SEARCH("Space Temp",Sheet1!$E$3:$E$20)),ROW(Sheet1!$E$3:$E$20),""),ROW(A1))-2),"",INDEX(Sheet1!$C$3:$C$20,SMALL(IF(ISNUMBER(SEARCH("Space Temp",Sheet1!$E$3:$E$20)),ROW(Sheet1!$E$3:$E$20),""),ROW(A1))-2))
    And
    =Sheet3!$A$4:INDEX(Sheet3!$A$5:$A$30,MATCH(LOOKUP(2,1/(Sheet3!$A$5:$A$30<>""),Sheet3!$A$5:$A$30),Sheet3!$A$5:$A$30,0))
    Please look at attatched.
    Regards, junho

    Re: Set Data Validation list to ignore blanks in source list


    Wow, that's incredible! Can you explain how the A1 works in the formula.


    =IF(ISERROR(SMALL(IF(ISNUMBER(SEARCH("Space Temp",Sheet1!$E$3:$E$20)),ROW(Sheet1!$E$3:$E$20),""),ROW(A1))-2),"",INDEX(Sheet1!$C$3:$C$20,SMALL(IF(ISNUMBER(SEARCH("Space Temp",Sheet1!$E$3:$E$20)),ROW(Sheet1!$E$3:$E$20),""),ROW(A1))-2))


    For some reason when I apply this to my workbook, it works for the first value, i.e. it returns "A AC-18 Room Temp" however changing the "A1" parameter in the other formulas for the other rows returns only blanks. Thanks again for your help!

    Re: Set Data Validation list to ignore blanks in source list


    Hi Juger,
    row(a1) is a parameter of function 'small'.
    SMALL(IF(ISNUMBER(SEARCH("Space Temp",Sheet1!$E$3:$E$20)),ROW(Sheet1!$E$3:$E$20),""),ROW(A1))
    small(row(e3:e20) if cell is included "Space Temp",1)
    so lowest value of rows having "Space Temp" in range Sheet1!$E$3:$E$20.
    Regards, junho

    Re: Set Data Validation list to ignore blanks in source list


    Thanks again for the explanation. Is there a way to automate the process? In other words, I would like for the drop down lists to automatically remove the blank values that are returned without having to do an array type of command or any input from the user. The user should just be able to copy & paste in the data base, and all the drop down lists will be updated without blanks.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login