Updating Data in Validation Lists

[Guest]

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.

 

[Guest]

If you want A1 to update auto based on value in A2, use a vlookup or
index/match in A1 to lookup A2. Don't use a DV.

 

[Guest]

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

 

[Max]

Barb,

I'm not sure that a dynamic range for the DV will do it here

To me, the OP's key point was:
Even with a dynamic range, this won't change what's already selected in A1.

---

 

[Guest]

Actually, I'm using a DV because I am referencing data in A1 from more than
one cell, i.e., A2, A3, and A4. However, when I leave A1 to display the A2
data, I would like it to update changes in A2, and like wise for the other
cells (A3, A4).

 

[Max]

Could you give examples to better illustrate your set-up. What exactly do
you have in A1, A2, A3, A4 ?

 

[Debra Dalgleish]

There's a sample workbook here that might help you:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0022 - Update Validation Selections'

 

[Max]

See Debra's response for the answer that you seek.

---