Data Validation - Drop down when cell is blank

  • Thread starter Thread starter mcolson
  • Start date Start date
M

mcolson

I am using data validation from a list in Excel 2007. When I click
the drop down arrow and the cell was previously blank, I do not see my
options. I have to scroll up to see my choices. I realize that it is
doing this because blank cells exist in my list and it jumps to the
first one when I click the down arrow, I then see what is below that
cell in the list which are more blank cells. Is there something that
I can do so that if the cell is blank, when I click the drop down
arrow, it will show me the top of the list first? I realize one work
around is to leave the top row blank. But others will be making
changes, and I do not want them to accidentally use that row.
 
If I understand you correctly, you have a volatile list that you are using
in the DV cell. I presume that you would like for the DV cell to display
only the remaining values in the list, and not the empty spaces. Is that
correct? If so, I would use a Worksheet_SelectionChange event macro to act
when that DV cell is selected. When that cell is selected, the macro would
take the existing list, blank cells and all, copy it to some out-of-the-way
place, sort that new list thereby removing all the blank spaces, and name
the new list the name used in the DV cell. The list displayed in that DV
cell would then be a list of only the constants (no spaces or blanks). The
list used by the DV cell need not be visible to the users. Does this sound
like something that fits what you want? HTH Otto
 
Back
Top