using range names in validation

[boris]

Would like to do validation and use a list that is
referenced by a named range. Any way to do this? My main
objective is to have validation expand and contract based
on the changing list, so if there is another, better way
of doing this, that's fine, too.

 

[Andy Brown]

"boris",

Go here
http://www.contextures.com/xlNames01.html#Dynamic

then here
http://www.contextures.com/xlDataVal01.html

Rgds,
Andy

 

[Andy Brown]

Hi Aladin.

=Sheet1!$A$2:INDEX(MATCH(REPT("z",255),Sheet1!$A:$A))

I tried this and got "too few arguments".

I wouldn't know where to start deciphering it, so ???

Rgds,
Andy

XL2K on XP Home.

 

[Aladin Akyurek]

It should be:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A))

Thanks pointing out.

 

[Andy Brown]

It should be:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A))
Thanks pointing out.

I was sure it was an oversight. Works fine -- for text. Old standard
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)
works for values, barring gaps. I wouldn't define something with gaps as a
list, but can see your formula could be useful in terms of "binding" used
ranges for cleaning up. I will make a note of it, but would probably stick
with ActiveSheet.UsedRange.Select
then redim to take out eg: header row.

Rgds,
Andy