The source box in a validation list can either contain an array of values or
use a range of cells in the worksheet when you have the option set to "LIST".
If you set up a validation list manually on the worksheet you can select the
source to be a range of cells on the same worksheet as the validation list.
Excel doesn't allow you to use a different worksheet.

If you need the VBA code for settting up the range just record a macro while
you perform the operation manually.

"tbd" wrote:

Greetings,
With VBA I'm currently specifying a long list of xlValidateList items
(formula1:= CSVlist) for each of many cells. There is only one list
(constructed at run-time), but there could be hundreds of values, and there
may be hundreds of cells for which the user will select a unique value. I'm
not sure, but I think Excel typically keeps a seperate xlValidateList for
each cell - if so, this will be very inefficient.

1) Is it possible for Excel 2002 to implement the Validation list by
reference to a data-structure or range - so that the "one list" only ever
exists in one place, but many cells refer to it?

2) Please confirm: when VBA executes the following assignment:

formula1:="=$n$1:$n$18"

, the expression "=$n$1:$n$18" is evaluated immediately(?)

Any help is appreciated,
Cheersr!