Validation List

[Guest]

Hi , I am using the example DV0021 - Update Multiple Validation Lists from
page http://www.contextures.com/excelfiles.html , could you please explain
me how the range is re-sized when some item is added ?

Thank you in advance.

 

[Guest]

Hi Angeles,

The list is resized using the COUNT and OFFSET functions. For example, the
name "FruitList" contains the function:

=OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1)

OFFSET has the form (Reference, Offset # of Rows, Offset # of Columns,
Height, Width) where Height and Width are optional. In the function above,
the start Reference is Lists!$A$1 and the number of rows in height is found
by counting the number of cells in range $A:$A that contain values (5 in this
case).

Thus, if additional values are entered in Lists!$A:$A, then the FruitList
will grow. The use of Dynamic Named Ranges becomes very helpful when dealing
with databases of unknown size.

If this is unclear, let me know.

Matthew Pfluger

 

[Guest]

The explanation is very clear , thank you very much.

Angeles