Data Validation - List
 

Hi everyone,

Is there anything / glitch that prevent the user from making data validation
as List in Excel?

I'm trying to make a form template that will have two different List in one
column. A1 is the List as Name for user easy to fill it out. A2 is the
header and A3:A100 is the a List of item selection.

The data validation of A3:A100 is fine, but I can't get A1 to works. Oh, by
the way, I also set A2 as autofill.

Any idea of why this happen?

I'm using Office 2004 for Mac.

thank you,
Neon520

Data Validation - List
 

First off, you should use a dynamic range for the List instead of always
pointing to the entire range A3:A100. Instead use
=OFFSET($A$3,0,0,COUNT($A$3:$A$100),1)

Secondly, the "name" of that dynamic range shouldn't matter to the end user.
You make it whatever you want and hide that name inside your data validation
rules. No one ever has to see it or care.

Third, you can only dynamically set the "name" of a range using VBA. The
name is static, but the range it points to can by dynamic. The only way to
keep changing the name is to use VBA, but as I said above, it's totally
unnecessary, so why bother?
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Neon520" wrote:

Hi everyone,

Is there anything / glitch that prevent the user from making data validation
as List in Excel?

I'm trying to make a form template that will have two different List in one
column. A1 is the List as Name for user easy to fill it out. A2 is the
header and A3:A100 is the a List of item selection.

The data validation of A3:A100 is fine, but I can't get A1 to works. Oh, by
the way, I also set A2 as autofill.

Any idea of why this happen?

I'm using Office 2004 for Mac.

thank you,
Neon520