Establishing Suitable Table and Sheet Protections

[mike_302]

I could potentially put together a sample workbook for this, but the nature of the problem seems like it's better simply described. The key features of the sample workbook wouldn't be immediately visible.

Short summary: I have a workbook with multiple sheets with the same sort of table on each. The table has multiple columns with various data validation and lookups setup in difference columns. Users interact with the workbook by inputting rows, where some of the data validation is helpful in populating some columns, but can be overridden; and the lookups help to auto-populate other columns. Finally, there is usually a good deal of copy-pasting rows/chunks of rows, and/or deleting rows.

The trouble I've had is:

1. It is easy for an unsuspecting user to copy-paste a row (with data validation and lookups in some of the columns) from one table, into another table on a different sheet. The data validation and lookups in each individual table are referring to different locations (Reference tables, lists, etc.), so then things easily become messy across tables. I would like to mitigate or avoid this problem altogether if possible.
2. Table formatting is getting messy through the copy-paste operations. I'm afraid that the original author of the workbook partly drew some lines overtop of the Excel Table formatted lines, and so when the table is expanded, or groups of cells are copy-pasted, the formatting that has been drawn over moves. I just want to reset things, but I don't want to deactivate the Tables (Convert to Range) and start over by renaming the Tables again. the Table names are called elsewhere in the workbook, so it'd be a pain. Can I reset Table formatting somehow?

 

[Jeffrey Mahoney]

Your going to have to create an area where users can add and remove records from the database without copying and pasting. Allow them to see the data and choose actions without allowing them to manually change the data. I'd have to see the tables to understand how that would work for you. In some cases I create a control board that allows the user to add, subtract, and filter the data, while the real database is on a hidden sheet.

For other occasions when I hade to allow the user to edit data directly, I would create a macro that would heal the formatting, especially Conditional Formatting, because that stuff gest real messy when copied.