Data Validation

W

Wayne

When validating a cell from a list of cells, the drop-down box
includes the blank cells in my list. Is there any way of only
including cells with data in?

Wayne
 
S

suchi

When you select the list to pick from, do not select blank
Cells (make sure you dont have hidden rows there)

good luck
 
S

Steve Andrews

Wayne,

I often do this by creating a reference string for the
valid range in a cell and using the INDIRECT( ) function.
My methodology involves the following steps:

a) Pick a column, say Z, which is beyond your work area.
b) Say your list is in $B$7:$B$1000. Assign
Z7 = if(B7="","",row())
c) Copy Z7 down to Z1000
d) Assign Z2=max($Z$7:$Z$1000) as the last row of a
nonblank entry in Column B.
e) Assign Z4="$B$7:"&ADDRESS(Z2,2) where the "2"
references Column B (the second column).
f) In Data Validation assign the source of data to be
INDIRECT(Z4)

I hope this is helpful.

Steve
 
Top