Establishing Suitable Table and Sheet Protections

mike_302

New Member
Joined
Oct 6, 2017
Messages
14
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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
2,112
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,181,708
Messages
5,931,592
Members
436,795
Latest member
mazigazi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top