Auto Update validation list?

[Eloise]

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?

 

[Pete_UK]

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]

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!

 

[Debra Dalgleish]

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

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

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

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

 

[Eloise]

Thanks, that's a very useful website!

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

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

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

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

 

[Debra Dalgleish]

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