If spelling of a word in a DV range changes, can values previously selected be updated?

[Arnold]

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

 

[Ken]

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

 

[Dave Peterson]

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]

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.

 

[Arnold]

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.

 

[Debra Dalgleish]

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]

Thanks for all your replies. Happy new year.

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'