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.
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
forum.ozgrid.com/index.php?attachment/37239/I don't see the "EDIT POST" in he gray band under the post. Can you describe where it is again?
Nevermind, found it. If you look at the workbook, Sheet 2, the drop down list shows blanks in between true values. How do I make those blanks go away so the list only shows the true values?
If I have a drop down list that references a column, and there are certain values that are highlighted in color. Is there a way for the drop down list to only show the values that are highlighted in color?
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.
