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

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




In article ,
LiAD wrote:

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.