How to Block Data Validation Override
You can setup a data validation list to limit entry to certain cells. But you will realize at some point that your data validation can be overridden by simple copy/paste. If you want your data validation to persist without coding macros, here is how to block data validation override.
There is a known solution for this issue, which is blocking copy/paste commands on workbook by use of VBA coding. But you need to be more than a bit Excel savvy to apply this solution. Also you might want copy/paste available or you just might not want a macro enabled workbook for a number of reasons.
My solution doesn’t actually blocks data validation override but renders values unusable in case of a copy/paste override attempt. So copy/paste is not an option for data validation override. Here is how to set it up:
We have a number of employees which we can assign tasks. Each employee has a certain capacity to spare so we can assign limited number of tasks to each one. Here is our employee capacity table:
Following is the picture of the list we are going to use to assign tasks to employees.
If you apply data validation list to column I using range B3:B9 as source, you can limit entries to our employee names. But this doesn’t cover employee capacity. So we need a more complex approach.
Setup another range (B16:B22) with following formulation:
This formula checks assigned tasks versus capacity and shows employee name if there is available capacity, results blank if capacity is filled. Now if you use this new range as source for your data validation list, you can restrict data entry with employee capacity.
But your data validation setup can still be overridden by simple copy/paste. If anybody wants to, they can copy “John” and paste it into your list 10 times. So all this setup can easily go to trash. To prevent this, we need to make an even more complicated setup (but nothing to be afraid of).
We are going to separate name input field from our task list. Then fetch inputted data into our task list with a conditional formula. And then we will lock our list’s name field for editing to ensure nothing can go wrong.
Above is a picture of our final setting. Column G has the same data validation setting above. It reads range B16:B22 for name values. Column I has following formula and is protected for editing. This If-And Combination Formula might seem a little scary to some but it is really not that complicated. I broke it down anyway. Formula of cell I3:
G3&” is overloaded.”))))))))
This formula simultaneously checks entered name against name list and whether it inside of available capacity. If either one of the conditions returns negative, it displays a custom error message. In this example, if you input John two times you will see John in the list (see the picture above). If you input John for a 3rd time (you can do so only by copy/paste) you will get an error message on the list (see the picture below).
You can download and inspect Override Data Validation example workbook for further understanding of the concept.
Note: Sheet1 is protected. You can unprotect it from REVIEW Ribbon. It is not password protected.