Excel Data Validation

[Guest]

I've got a spreadsheet on which I'm using Data Validation for a number of
fields. A few questions:
1. Where is the best place on the worksheet to put the data validation lists
so that they won't get deleted?
2. How can I protect the data validation lists from getting deleted?
3. When users insert/delete rows, the data validation fields get deleted as
well. How can I 'format' a range of rows so that the data validation always
takes effect even if rows get inserted/deleted/or the fields get all
blanked-out?

 

[Guest]

For greater protection you can put the list on another worksheet in the same
workbook, provided that you name the list. Enter the list items, select the
entire list, then Insert / Name / Define. Give the list a name, ex DV_List.
Then on the sheet where items are to be selected from the list, set the
validation to allow a list with the source set to =DV_List.
You could also hide the worksheet containing the list and then protect the
workbook.

 

[Guest]

Many thanks. I had tried that, using the naming convention of
Sheet2!data_range, and it would not allow me to code it that way. I did not
realize I had to give the data_range a name to be able to reference it from
another worksheet in the same workbook. Again, thanks.