Data Validation that isn't affected when data pasted in

Hi,

I'm trying to set rules up in an excel so that when data is pasted into the cells, if it's incorrect it won't allow the user to paste the data. However, it seems that when i set the rules up and paste the data, it just completely removes the data validation rules. Is there a way round this?

Thanks

I think as stated this would be hard to do as the validation is based on cells in the spreadsheet, so the data has to be pasted to flag concerns.

You cna paste as values which preserves the rules, but then the data has been pasted, what next?

You could paste data into a separate sheet, evaluate the data and then paste it into a master sheet if it is ok

However a sample and guidance is needed
Is data overridden or appended to the end in your process?
If there is a problem, does it effect the cells, the row or the whole batch?

I feel the solution will involve a vba approach

Hi Both,

Thanks for the reply. I think you're right, it should be done using VBAs but I don't think that's a long-term solution for us. I think it'll have to be more down a different path for us but thank you for your time nonetheless

Alex