How do I make a Data Validation drop-down exclude blanks?
- by Iszi
Related: How can I use non-adjacent cells on another sheet for a Data Validation drop-down, and only show non-blank values?
For now, I've worked around the above problem by re-arranging my sheet so all the Data Validation Source cells are in one range. I'm leaving the above question open though, because I think it still poses an interesting problem.
However, the issue now is that the Data Validation drop-down isn't working in the way I expected it to (and how I believe others are telling me it should). Even though I've got everything into one named range, Excel still shows blanks in a drop-down that references that range.
Setup:
Sheet 1
A1= (blank) B1= Header
A2= 1 B2= Value1
A3= 2 B3= Value2
A4= 3 B4= Value3
A5= 4 B5= (empty)
A6= 5 B6= (empty)
A7= 6 B7= (empty)
Sheet1!B2:B7 is named Validation
Sheet2!A1 is set to use Data Validation with a Source =Validation, and in-cell drop-down. The drop-down in Sheet2!A1 shows:
Value1
Value2
Value3
.
.
.
(Dots represent blank lines)
How can I get rid of these blank lines in the in-cell drop-down, while still including Sheet1!B5:B7 in the Data Validation Source?
Note: I nuked the sheet, and tried it again without column A from Sheet1 (putting values from column B in the above example into column A), and it worked fine. Adding Column A back though, brought the blanks back into the Data Validation drop-down. What do I need to do to keep column A as I want it and keep the in-cell drop-down clean?
