Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update Validation List | Excel Discussion (Misc queries) | |||
auto updating list | Excel Worksheet Functions | |||
validation list | Excel Discussion (Misc queries) | |||
Update master list with other lists | Excel Worksheet Functions | |||
Refresh a Validation List? | Excel Discussion (Misc queries) |