Copy/paste overwrites data validation

[Dangerous_Dave]

I have a file saved on our company intranet page with 20 odd column headings and the rest of the sheet blank, which is used as a template to collect performance data from our sites around the world. Each site opens the file, having already rearranged their data to match the order shown by the column headings on the template, and adds thousands of lines of data.

This almost works perfectly. What I have problems with is that some of the lazier sites aren't quite so diligent with the data they supply - mispelling category names or using non existent ones, or dates that don't exist (30th February) or not putting some data in at all - and it takes me a few hours to put it all right. So, I need to use data validation on several of the columns.

However, the well known problem that I've seen banded around many forums is that a user can paste thousands of lines of data onto the template and completely overwrite the data validation. Is there any way I can overcome this without using any VBA or am I left with no option ??? If I do need any code, would someone mind offering me a working solution - I've seen several different theories on forums but don't know which ones to pick from (and I've seen nothing that mentions a validation for date).

Any help would be much appreciated.

Thanks

DD

 

[Andrew Poulsom]

It's not possible without VBA. Maybe this will help you:

http://spreadsheetpage.com/index.php/tip/ensuring_that_data_validation_is_not_deleted/

 

[Dangerous_Dave]

Would that stop the users from pasting anything at all ??? They won't want to type in a line at a time, they'll want to post a thousand or so lines in one go.

Is there code that allows the user to paste and ensures that the data pasted is valid but that shows an error when one or more of the cells does not match the validation criteria ???

Thanks

 

[Andrew Poulsom]

Tell them to use Paste Special Values.

 

[Dangerous_Dave]

Sorry, that wasn't infallible either. It didn't delete the data validated cells but it did allow me to post invalid data. Is this task beyond Excel's capability ???



While we're still researching that one, I had an idea for an alternative techniquewhich was to maybe run some code that, upon save, will run a macro first, check that all cells contain valid data, and if so, to save. If not, point out to the user that he/she has entered invalid data and refuse to save until it's corrected. Is this maybe too ambitious ???

Cheers

 

[Andrew Poulsom]

You may find this of interest:

http://www.dailydoseofexcel.com/archives/2007/12/17/catching-paste-operations/