Data validation: using Custom, NOT List, to get non-blank cells

[Guest]

Hi all,

This is a bit tricky question. We all know that cell with Data Validation's
List includes items contained in a specified named range. Fine.

I, instead, want to do it with Custom using some kinf of formula, so that
users do NOT need to change the named range when a new item was added. Is
this possible?

I have some items in one column and thinking of going from top, say, A1
until the last non-blank cell downward, say, A10. How can I specify this in
Data Validation's custom option?

Thanks for your time.

 

[Leo Heuser]

Hi Tetsuya

Here's one way:

Choose "List" and enter this formula:

=OFFSET(A1,,,COUNTA($A:$A))

Only use column A for elements of the list.

 

[Stephen Bullen]

Hi Tetsuya,

This is a bit tricky question. We all know that cell with Data Validation's
List includes items contained in a specified named range. Fine.

I, instead, want to do it with Custom using some kinf of formula, so that
users do NOT need to change the named range when a new item was added. Is
this possible?

This can be achieved by using a dynamic name to refer to the list. So instead
of the name being, say, =$A$1:$A$10, you define it as
=OFFSET($A$1,0,0,COUNTA($A:$A),1) (assuming there's nothing else in column
A).

That name will then automatically refer to however many items are in the list
that starts at A1, and the name can be used to drive DV's list.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk