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

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

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

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.

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.

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

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