Drop Down that can have data pasted into it without losing the drop down

[Jasper27]

Hi,

I have a sales report I want our clients to fill out. When they tell us the product # they sold I also wan them to tell us what that product is using a drop down the the next cell. However I know some of them with just copy and paste the data in which will remove the drop down if done by data validation. I also want the sheet to validate that information against the list in the drop down.

So if the drop down has Mens T-shirt, Womens T-shirt, Hat, Wrist Watch

and they paste in T-shirt it should let them know that is not on the list.

Would it be possible to have it linked to a button so after they fill out possibly hundreds of rows they can hit a button to validate?

 

[Rijnsent]

Hi Jasper, welcome to the forum. What I would start with if I were you: try to record a macro doing just that. So place validation on a (hidden) cell, copy-paste special (validation) over the range where the dropdowns are. Next, the validation menu has a button that's called "Circle Invalid Data", you could use that to show the user which ones are wrong. Do give the macro recorder a try and adjust that code. If you get stuck with code, do post it here so people can help you improve that code (note: if you do, please use the CODE brackets).