Updating Data in Validation Lists
 

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.

Updating Data in Validation Lists
 

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"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.

Updating Data in Validation Lists
 

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.

Updating Data in Validation Lists
 

Barb,

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

To me, the OP's key point was:
I have to click on the drop down box
and click on the new data for it to display.

Even with a dynamic range, this won't change what's already selected in A1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Updating Data in Validation Lists
 

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" wrote:

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"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.

Updating Data in Validation Lists
 

Could you give examples to better illustrate your set-up. What exactly do
you have in A1, A2, A3, A4 ?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"kivikatz" wrote in message
...
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).

Updating Data in Validation Lists
 

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'

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.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Updating Data in Validation Lists
 

See Debra's response for the answer that you seek.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---