I've done this using a dynamic range in the past. Let's say a title for the
validation list is in Sheet1!A1 and the list is in cells Sheet1!A2:An (I'm
also assuming that there is nothing after the last entry to the bottom of the
worksheet)

Do this:

Insert - Name - Define
Enter a name for the range .. Let's call it ValidationList

In the Refers to section, put this:

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

and add the named range.

Sheet1!$A$1 is the reference to start the range
1 is the # of rows down to offset the range from the reference.
0 is the # of columns to offset the range from the reference
Counta() is the count of the # of rows to include in the range
1 is the # of columns to include in the range

TO check that it's correct, you can do CTRL G and type in ValidationList.

Once the list is working properly, you can set up your data validation to be
a list and the source is =ValidationList



"kivikatz" wrote:

Is there a way to automatically update data in a validation list when the
data changes in the sourced cells? for instance if A1 is the validation list
that display data sourced from A2, if data in A2 changes, I would A1 to
display the new data. Currently, it does not, and only shows the old data.
I have to click on the drop down box and click on the new data for it to
display. Thanks.