Updating Data in Validation Lists

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
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
 
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.

---
 
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).
 
Could you give examples to better illustrate your set-up. What exactly do
you have in A1, A2, A3, A4 ?
 
Back
Top