Data Validation

  • Thread starter Thread starter Wayne
  • Start date Start date
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
 
When you select the list to pick from, do not select blank
Cells (make sure you dont have hidden rows there)

good luck
 
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
 
Back
Top