Data Validation (List)

[mitank]

I have a series of text written in Sheet1, coloumn A. From A1 to A20.

In Sheet 2 coloumn B5 to B10 I would like to create a list type data validation in such a way that it searches till the end of the last text written ie. A20.
Next the moment I write any data in Sheet1 A21, automatically the data validation list gets displayed till A21.

[redocs]

You can do that by defining a (dynamic) named range

Naming a range with the following formula in the "refers to" box and then pointing your data validation list to this named range should do the trick.

=OFFSET(Sheet1!$A$1;0;0;COUNTA(Sheet1!$A:$A);1)