Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If spelling of a word in a DV range changes, can values previously selected be updated?
I'm accustomed to databases, in which this is easy. After browsing this
group, it appears that Excel cannot do this? I have ranges on Sheet1, and data validation list boxes on Sheet2 pull in the data from Sheet1. The data are only names, like firstname and lastname. I know that a user will, by accident, misspell a name in Sheet1, then set a bunch of cells in Sheet2 with the misspelled name, then realize it, then correct the name in Sheet1, then go to Sheet2 and say, "Hmmm, I thought I corrected the spelling of that name." Is there a way to automatically update the values? FYI--I expect that the majority of misspelled names will only be a letter or two off, exclude a period, etc.--not be an entirely different name. Thanks for helping if you can. Arnold |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If spelling of a word in a DV range changes, can values previously selected be updated?
It seems like you could either:
1. Do a search and replace on the relevant area in sheet2 anytime you make a change to the name list on sheet1. or 2. Link the names on sheet2 to the validation list on sheet1 through a lookup. That way the names will be dynamically synchronized with the list and the manual search and replace would be unnecessary. or 3. Have code in triggered by a workbook event to automatically trigger the search and replace in the other sheet. This would be feasible, but, a little tricky since you would have to keep track of what the name was before you changed, so the code would know what to replace. Good luck. Ken Norfolk, Va On Dec 30, 9:38*am, "Arnold" wrote: I'm accustomed to databases, in which this is easy. After browsing this group, it appears that Excel cannot do this? I have ranges on Sheet1, and data validation list boxes on Sheet2 pull in the data from Sheet1. The data are only names, like firstname and lastname. *I know that a user will, by accident, misspell a name in Sheet1, then set a bunch of cells in Sheet2 with the misspelled name, then realize it, then correct the name in Sheet1, then go to Sheet2 and say, "Hmmm, I thought I corrected the spelling of that name." Is there a way to automatically update the values? *FYI--I expect that the majority of misspelled names will only be a letter or two off, exclude a period, etc.--not be an entirely different name. Thanks for helping if you can. Arnold |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If spelling of a word in a DV range changes, can values previouslyselected be updated?
You're right. Excel won't do it by itself.
Maybe you can apply data|filter|autofilter to the range with the names and filter to show just the bad names and fix them that way. Arnold wrote: I'm accustomed to databases, in which this is easy. After browsing this group, it appears that Excel cannot do this? I have ranges on Sheet1, and data validation list boxes on Sheet2 pull in the data from Sheet1. The data are only names, like firstname and lastname. I know that a user will, by accident, misspell a name in Sheet1, then set a bunch of cells in Sheet2 with the misspelled name, then realize it, then correct the name in Sheet1, then go to Sheet2 and say, "Hmmm, I thought I corrected the spelling of that name." Is there a way to automatically update the values? FYI--I expect that the majority of misspelled names will only be a letter or two off, exclude a period, etc.--not be an entirely different name. Thanks for helping if you can. Arnold -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If spelling of a word in a DV range changes, can values previously selected be updated?
I've just searched on lookup, vlookup and hlookup; have not used these
before, and don't off hand know how to link cells with drop-down lists on one sheet with the data on another. Do you know of a good thread or site? Code to search and replace would have to: --identify if a value was originally in a cell if a user enters it, --capture that value, --on cell lost focus, determine if the original value changed, --if so, then select the other worksheet, --perform search and replace all identical values, --prompt user and inquire if its really what should be done, and --return to the data entry sheet. Correct? Any ideas? Thanks again. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If spelling of a word in a DV range changes, can values previously selected be updated?
I've just searched on lookup, vlookup and hlookup; have not used these
before, and don't off hand know how to link cells with drop-down lists on one sheet with the data on another. Do you know of a good thread or site? Code to search and replace would have to: --identify if a value was originally in a cell if a user enters it, --capture that value, --on cell lost focus, determine if the original value changed, --if so, then select the other worksheet, --perform search and replace all identical values, --prompt user and inquire if its really what should be done, and --return to the data entry sheet. Correct? Any ideas? Thanks again. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
If spelling of a word in a DV range changes, can values previouslyselected be updated?
There's a sample file here that uses programming to update changed items:
http://www.contextures.com/excelfiles.html Under Data Validation, look for 'DV0022 - Update Validation Selections' Arnold wrote: I'm accustomed to databases, in which this is easy. After browsing this group, it appears that Excel cannot do this? I have ranges on Sheet1, and data validation list boxes on Sheet2 pull in the data from Sheet1. The data are only names, like firstname and lastname. I know that a user will, by accident, misspell a name in Sheet1, then set a bunch of cells in Sheet2 with the misspelled name, then realize it, then correct the name in Sheet1, then go to Sheet2 and say, "Hmmm, I thought I corrected the spelling of that name." Is there a way to automatically update the values? FYI--I expect that the majority of misspelled names will only be a letter or two off, exclude a period, etc.--not be an entirely different name. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
If spelling of a word in a DV range changes, can values previously selected be updated?
Thanks for all your replies. Happy new year.
Debra Dalgleish wrote: There's a sample file here that uses programming to update changed items: http://www.contextures.com/excelfiles.html Under Data Validation, look for 'DV0022 - Update Validation Selections' Arnold wrote: I'm accustomed to databases, in which this is easy. After browsing this group, it appears that Excel cannot do this? I have ranges on Sheet1, and data validation list boxes on Sheet2 pull in the data from Sheet1. The data are only names, like firstname and lastname. I know that a user will, by accident, misspell a name in Sheet1, then set a bunch of cells in Sheet2 with the misspelled name, then realize it, then correct the name in Sheet1, then go to Sheet2 and say, "Hmmm, I thought I corrected the spelling of that name." Is there a way to automatically update the values? FYI--I expect that the majority of misspelled names will only be a letter or two off, exclude a period, etc.--not be an entirely different name. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate values in an unlimited range of cells in a column WITHOUTalso calculating values that had previously been filtered AGAINST? | Excel Discussion (Misc queries) | |||
Excel changes spelling of word | Excel Worksheet Functions | |||
Macro to concatenate previously selected cells | Excel Programming | |||
Clearing what you have previously selected | Excel Programming | |||
Selection the previously selected cell | Excel Programming |