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:
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:
- 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.
- 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?