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.
A1= (blank) B1=
4 B5= (empty)
5 B6= (empty)
6 B7= (empty)
Sheet1!B2:B7 is named
Sheet2!A1 is set to use Data Validation with a Source
=Validation, and in-cell drop-down. The drop-down in
(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
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?