This formula will probably work better

=OFFSET(J1,MIN(IF($J1:$J100<"",ROW($J1:$J100)))-1,,SUMPRODUCT(--($J$1:$J$100<"")))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"David" wrote in message
...
Thanks Bob, that WORKS!
With the first selector as 3, the frop down box starts as 3 like it should
(THANK YOU!) but there are two blanks spaces at the end (I presume these
are
the spaces left over). Any thoughts on that? I can live with it, but would
like to clean it up!
Many thanks again!!

"Bob Phillips" wrote:

That is not what the Ignore Blank box is for.

If you want a limited list, you have to create it elsewhere on the
spreadsheet, without the blanks, and reference that in the new DV.

OR you could use a formula like

=OFFSET(J1,MIN(IF($J1:$J100<"",ROW($J1:$J100)))-1,,COUNTA(J1:J100))

in the new DV.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"David" wrote in message
...
I have a Range (Gold) in I1:10 with data 1 to 10 in each cell.
I have a cell (F4) that is the drop down selector fot the Gold Range.
In column J, I have the formula =IF($F$4<=I1,I1,"") so that no number
will
appear less than the number selected in F4
I then have another Range for J1:J10 with a cell in G4 to select the
numbers
that appear in that range (this is the one that only shows the numbers
equal
to or higher than the number selected in F4).
All that works OK, but the drop down box in G4 shows the blank spaces
in
column J. In the validation, I have Ignore Blank checked, but the blank
spaces are still showing up in the drop down list.

Thank you...this is driving me NUTS!