Data validation not working when paste special values

Tashat

Board Regular
Joined
Jan 12, 2005
Messages
65
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,957
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 12, 2005
Messages
65
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,796
Messages
5,638,385
Members
417,025
Latest member
MusterDuster

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