Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation Updates
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation Updates
Edit,Replace (Ctrl + H) Find d, Replace with:= z
"LiAD" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation Updates
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 "Billy Liddel" wrote: Edit,Replace (Ctrl + H) Find d, Replace with:= z "LiAD" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation Updates
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation Updates
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation Updates
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... In article , LiAD wrote: So I would like excel to - ensure that future entries are mice - change all old entries from mouse to mice |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation Updates
Ok thanks for trying anyway
"JE McGimpsey" wrote: 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... In article , LiAD wrote: So I would like excel to - ensure that future entries are mice - change all old entries from mouse to mice |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation Updates
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 On Wed, 14 Jan 2009 08:54:47 -0800, LiAD wrote: Ok thanks for trying anyway "JE McGimpsey" wrote: 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... In article , LiAD wrote: So I would like excel to - ensure that future entries are mice - change all old entries from mouse to mice |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data updates by clicking and dragging bar graph | Charts and Charting in Excel | |||
Data Updates With Date | Excel Discussion (Misc queries) | |||
Macro: Updates a formula or value when a data is entered. | Excel Worksheet Functions | |||
A Chart that Updates Automatically as Data Size Changes? | Charts and Charting in Excel | |||
Is there a way to automatically add columns as the data updates? | Excel Worksheet Functions |