Ignore Blank in Validation not working

[David]

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!

 

[Bob Phillips]

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]

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!!

 

[Gord Dibben]

David

You misunderstand the purpose of "ignore blanks"

Blank Cells in Source List

If the source list is a named range that contains blank cells, users may be able
to type any entry, without receiving an error message. To turn prevent this:

Select the cell that contains a data validation list
Choose Data|Validation
On the Settings tab, remove the check mark from the Ignore blank box.
Click OK
Note: If the source list contains blank cells, and is a range address, e.g.
$A$1:$A$10, it will block invalid entries with Ignore blank on or off.

Maybe check out Debra's site for creating dynamic named ranges so's blank cells
are not included.

http://www.contextures.on.ca/xlNames01.html#Dynamic

And this page may help.

http://www.contextures.on.ca/xlDataVal13.html


Gord Dibben MS Excel MVP

 

[Bob Phillips]

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)