Auto Update validation list?
 

I've created a file with my lists on one worksheet (called FORMAT) and my
other worksheets pulling from that sheet to get drop-down options in each
cell. I've gone through an manually selected the cells to show the value for
each field, based on the drop-down list options. Now I want to make some
changes to the wording within the drop-downs (for instance, I saw a
mispelling).

Is there a way to make this automatically update in each of the sheets
pulling from the FORMAT sheet? Or do I now have to go through each sheet and
manually re-select the corrected word?

Auto Update validation list?
 

If the drop-down lists on the other sheets are getting the data from
your FORMAT sheet (through named ranges), then you only need to make
the changes to the FORMAT sheet. One way if you have made the same
mis-spelling several times is to highlight all the cells on the FORMAT
sheet (click the intersection between the row and column identifiers to
highlight all cells) then do a Find & Replace (CTRL-H) and type your
mis-spelt word in the Find What box and the correctly-spelt word in the
Replace With box then click Replace All.

Hope this helps.

Pete

Eloise wrote:
I've created a file with my lists on one worksheet (called FORMAT) and my
other worksheets pulling from that sheet to get drop-down options in each
cell. I've gone through an manually selected the cells to show the value for
each field, based on the drop-down list options. Now I want to make some
changes to the wording within the drop-downs (for instance, I saw a
mispelling).

Is there a way to make this automatically update in each of the sheets
pulling from the FORMAT sheet? Or do I now have to go through each sheet and
manually re-select the corrected word?

Auto Update validation list?
 

That's what I thought would happen, but because I've already entered data
using drop-downs, any updates aren't automatically updating those that have
already been chosen.

For example, one of the lists has these options:
0°C
20°C
50°C
70°C

I needed to change 70°C to 80°C. However, since I had already gone through
and selected 70°C on a number of the cells that are pulling from the FORMAT
sheet, those cells still list 70°C, even though it's no longer an option in
the drop-down on those cells.

Does that make sense? If anyone knows, please let me know.

In the meantime, I'll just go through and do a Find and Replace, but it just
seems like there should be an automatic update on the validated lists.

Thanks!


"Pete_UK" wrote:

If the drop-down lists on the other sheets are getting the data from
your FORMAT sheet (through named ranges), then you only need to make
the changes to the FORMAT sheet. One way if you have made the same
mis-spelling several times is to highlight all the cells on the FORMAT
sheet (click the intersection between the row and column identifiers to
highlight all cells) then do a Find & Replace (CTRL-H) and type your
mis-spelt word in the Find What box and the correctly-spelt word in the
Replace With box then click Replace All.

Hope this helps.

Pete

Eloise wrote:
I've created a file with my lists on one worksheet (called FORMAT) and my
other worksheets pulling from that sheet to get drop-down options in each
cell. I've gone through an manually selected the cells to show the value for
each field, based on the drop-down list options. Now I want to make some
changes to the wording within the drop-downs (for instance, I saw a
mispelling).

Is there a way to make this automatically update in each of the sheets
pulling from the FORMAT sheet? Or do I now have to go through each sheet and
manually re-select the corrected word?

Auto Update validation list?
 

If you have already changed the source lists, you could use EditReplace
to fix the entries on the other worksheets.

For future changes, you could try programming, as in the example shown he

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0022 - Update Validation Selections'

Eloise wrote:
I've created a file with my lists on one worksheet (called FORMAT) and my
other worksheets pulling from that sheet to get drop-down options in each
cell. I've gone through an manually selected the cells to show the value for
each field, based on the drop-down list options. Now I want to make some
changes to the wording within the drop-downs (for instance, I saw a
mispelling).

Is there a way to make this automatically update in each of the sheets
pulling from the FORMAT sheet? Or do I now have to go through each sheet and
manually re-select the corrected word?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Auto Update validation list?
 

Thanks, that's a very useful website!


"Debra Dalgleish" wrote:

If you have already changed the source lists, you could use EditReplace
to fix the entries on the other worksheets.

For future changes, you could try programming, as in the example shown he

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0022 - Update Validation Selections'

Eloise wrote:
I've created a file with my lists on one worksheet (called FORMAT) and my
other worksheets pulling from that sheet to get drop-down options in each
cell. I've gone through an manually selected the cells to show the value for
each field, based on the drop-down list options. Now I want to make some
changes to the wording within the drop-downs (for instance, I saw a
mispelling).

Is there a way to make this automatically update in each of the sheets
pulling from the FORMAT sheet? Or do I now have to go through each sheet and
manually re-select the corrected word?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Auto Update validation list?
 

You're welcome! Thanks for letting me know that it helped.

Eloise wrote:
Thanks, that's a very useful website!


"Debra Dalgleish" wrote:


If you have already changed the source lists, you could use EditReplace
to fix the entries on the other worksheets.

For future changes, you could try programming, as in the example shown he

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0022 - Update Validation Selections'

Eloise wrote:

I've created a file with my lists on one worksheet (called FORMAT) and my
other worksheets pulling from that sheet to get drop-down options in each
cell. I've gone through an manually selected the cells to show the value for
each field, based on the drop-down list options. Now I want to make some
changes to the wording within the drop-downs (for instance, I saw a
mispelling).

Is there a way to make this automatically update in each of the sheets
pulling from the FORMAT sheet? Or do I now have to go through each sheet and
manually re-select the corrected word?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html