Data Validation Updates

[LiAD]

Hello,

I start with a list of text, a, b, c, d, e, f, g etc that I turn into a
drop down selection list using the data validation. The user then fills in
the sheet with whatever other fields they need.

However supposing I need to change d to z on the input list I am stuck with
data based on the old list. Is there a way that when I update the inputs I
can get excel to automatically go back through the filled in data and replace
all d with z in the table?

Thanks

 

[Billy Liddel]

Edit,Replace (Ctrl + H) Find d, Replace with:= z

 

[LiAD]

Hi,

This works fine but do you know of some way I can make it automatic? As
soon as I change d to z in the input column driving the validation that it
changes all the old entries.

Thanks

 

[JE McGimpsey]

One way:

Enter your list values in some cells (perhaps in another sheet, which
you can later hide.

Name the range (either select the cells and type a name in the Name box
of the formula bar, or create a static or dynamic range using
Insert/Name/Define).

In your validation, use

=MyListName

as your list criterion.

You can then make changes in the list and they will be reflected in the
dropdowns.

Note that they will not invalidate previously valid values.

If you need more help with dynamic ranges, see

http://cpearson.com/excel/named.htm#Dynamic

 

[LiAD]

Thankyou for your help although I suspect this is how I have already created
the ranges and validation. For future entries this poses no problem its the
old ones that are getting me stuck.

An example;

Dog, hen, cat, mouse is my first list to use. The user against each animal
then picks their favourite food for six months, biscuits, bread, cheese etc,
each animal has 5 types of food say. I now have a table that looks like

A B
dog biscuits
hen feed
mouse cheese etc etc for 600 columns (hungry animals)

One day i realise i didnt want to write mouse, i actually wanted to write
mice. So I change the input list and then any future entries will be mice
not mouse. However my last three months of data will record still be
recorded as mouse. Mouse is no longer valid data.

So I would like excel to
- ensure that future entries are mice
- change all old entries from mouse to mice

I have reems and reems of data that is then taken into other tables and
charts so if I can't automate the old entry change I need to do it manually
and I'm sure to miss some.

Thanks for your help

 

[JE McGimpsey]

You'd probably have to use VBA to change your list, then.

Once the list is changed, there's no data to indicate what the old value
is. For instance if the list is

Moose
Mouse
Mousse

and you change Mouse to Mice, there's no good way for XL to know which
entry you changed.

OTOH, for the number of times you're likely to use a macro, you could
probably just as easily select your cells and do a Find/Replace...

 

[LiAD]

Ok thanks for trying anyway

You'd probably have to use VBA to change your list, then.

Once the list is changed, there's no data to indicate what the old value
is. For instance if the list is

Moose
Mouse
Mousse

and you change Mouse to Mice, there's no good way for XL to know which
entry you changed.

OTOH, for the number of times you're likely to use a macro, you could
probably just as easily select your cells and do a Find/Replace...

 

[Gord Dibben]

Debra Dalgleish has a workbook for this.

DV0022 - Update Validation Selections -- If you change an item in a data
validation source list, the worksheet may show previously selected items.
Event code can update the worksheet when you update the source list.
DataValUpdate.zip 10kb Updated 07-Sep-11

http://www.contextures.on.ca/excelfiles.html#DataVal


Gord Dibben MS Excel MVP