Data validation not working when paste special values

[Tashat]

Hi everyone,

I have done some goggling about this, but not much seems to be relevant to Excel 365. I have data validation set up on various columns, however, when I paste, special values, the data validation doesn't work and it allows me to paste incorrect data in. Is there any functionality in 365 that prevents this? I don't want to use VB ideally and my users need to be able to paste in thousands of rows. I just want to make sure they paste into the correct columns. I have the worksheet protected so they can only paste into certain columns.

Thanks in advance

 

[Joe4]

Copy/pasting will override your Data Validation with the formatting of the cells you are copying from.
So Data Validation doesn't work all that great if you allow people to copy/paste data instead of making them enter it in manually.

Paste special values does not remove the Data Validation, but also does not validate the entries you are copying in.
See here: Data Validation - Why does it not work when pasting values?

Though you may not like it, I think your two best option would be VBA.
Otherwise, you may need to rethink your whole process.

 

[Tashat]

Copy/pasting will override your Data Validation with the formatting of the cells you are copying from.
So Data Validation doesn't work all that great if you allow people to copy/paste data instead of making them enter it in manually.

Paste special values does not remove the Data Validation, but also does not validate the entries you are copying in.
See here: Data Validation - Why does it not work when pasting values?

Though you may not like it, I think your two best option would be VBA.
Otherwise, you may need to rethink your whole process.

Thank you so much for your help. I expected that would be the case, I was just secretly hoping this had been 'fixed' in 365. Shame. Thank you though. I'll consider VBA.