Data Validation

[LeeCC]

Hello! Friends:

Appreciate if anyone could help on the followings (EXCEL 2003):

My data are stored in Col A (say, A1.A10)

Under Data/Validation/Setting/Allow List/Source, I give a range of A1.A50 to
cater for future data expansion. That is A11.A50 are blanks at this time.

Question: Even after checking both boxes "Ignore Blank" and "In-cell
dropdown", I still get the blank cells in the drop-down list. Why is it so?
And how to remove the blanks from the drop-down list?

Many Thanks.


Lee CC

 

[John C]

Debra Dalgleish has a very helpful site:
http://www.contextures.com/xlDataVal01.html
Look around in it. At the bottom, if you click on Data Validation Tips, then
select Create Dynamic Lists, I think this is exactly what you want.

 

[Gord Dibben]

You misunderstand the purpose of "ignore blanks"

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 if "ignore
blanks" is checked.

Not designed to leave out blank cells in a list as you have found.

See John's reply about creating a dynamic range as the best way to go.


Gord Dibben MS Excel MVP

 

[LeeCC]

Dear Gentlemen:

Thanks for all the helpful advice.


Lee CC

 

[KC Rippstein hotmail com>]

I tried this out and it's not quite doing what I want so I'm hoping to piggy
back off this same subject and go a step further.

My data area has a list with some blank rows throughout. So I've created a
separate grid way off to the right in columns AD7:AG21 with array formulas.
So while my data area may have a blank cell in T7 and the first entry is in
T8, the T8 entry is actually correctly listed in AE7 as the first valid entry
for my list of account managers. AE8 pulls the next account manager from
cell T9, AE9 pulls the next account manager from T11 (since T10 was blank),
and that's the end of my valid entries from column T, so in AE10:AE21 the
array formulas return nothing.

Now I'd like to have a dynamic named range which pulls just AE7:AE9 as my
AMList. I tried Debra's dynamic named range formula
=OFFSET(Summary!$AE$7,0,0,COUNTA(Summary!$AE$7:$AE$21),1) but it keeps
including the blanks (AE10:AE21). It works fine for truly blank cells but
not for cells with an array formula in them.

 

[KC Rippstein hotmail com>]

Found the answer at http://www.ozgrid.com/Excel/DynamicRanges.htm

=OFFSET(Summary!$AE$7,0,0,MATCH("*",Summary!$AE$7:$AE$21,-1),1)