Allowing pasting without overriding Validation

[defreeze]

I have a very large spreadsheet used for data entry at multiple research sites, and it is full of almost every kind of validation. I am making some major changes and updates to the conditional formatting as well as the validation (for instance, all missing data codes are being changed from '999' to '9999').

I need to paste all the old data (enormous) into the new version, but it is obviously causing the validation to revert back to version 1 because of the pasting. I know there is a way to "lock" the validation by disabling the paste function with code, but I need to be able to use paste. Why is there no way to simply paste the actual values without carrying the validation with it?? If I have to reenter the data manually cell by cell it will literally take me one year.

Is anyone able to help me with locking data validation, or suggest some other way to paste values into the cells without reverting to the previous validation?

 

[erik.van.geit]

Hi, defreeze
Welcome to the Board !!!!!

did you try this
copy the date
rightclick
select "pastespecial"
check "values"

kind regards,
Erik

 

[defreeze]

THANK YOU ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ray: ray: :wink: ray: ray:

I feel like an idiot now, but you have no idea how much you've helped me!

 

[erik.van.geit]

HAHAHAHA

You're <font face="Courier New" size="+1" color="blue" style="background: url(http://www.balloondoggies.com/vonpookie/misc/sparkle.gif)">WELCOME !!!!</font>