Validation of pasted values

[ddubbs]

Hi all,

Hoping you can help me. I've been looking everywhere, and all I can find are methods to prevent users from pasting over data validation cells. That's not what I am trying to accomplish.

This is a template that needs to be filled out by 50 people. They take information from their personal spreadsheets and dump it in here. Being able to copy/paste data is imperative. I also still need data validation on certain columns because this master spreadsheet will feed many systems that require specific text.

I have attached a sample part of my spreadsheet. *Never mind, cannot attach files..
Download link: Wikisend: free file sharing service


My only ideas:
Find code to lock paste shortcuts to special paste values only. Use another event to trigger validation of cell values. If a cell value does not match anything in the range, then change cell bgcolor to red and display error "Check data; Values must match those found on PickList sheet."

Thanks in advance for any help.

 

[ddubbs]

Any takers? All search results turn up old and attachments/links have expired.

I've also cross-posted this to Validation of pasted cells.

My thought is to not enforce the data validation from the picklists, and let the user enter whatever they want. However, when they enter something that is not found on the picklists, the box turns red notifying the user that they need to fix it.

Thanks again.

 

[J.Ty.]

Hi,

As far as I understand, your problem is that copy/paste values do not undergo data validation in cells.

1) There is a simple tool for that. If you open the standard "Data validation" menu there is an option "Circle invalid data" there. If you click it, it will put red ovals around every cell with invalid data. If you go to this cell, you can then correct the value by choosing one of the acceptable values from the drop-down list. After finishing, you can switch the circles off.
2) You can use conditional formatting to highlight data that offends the rules you want to enforce. The most expressive form is "Highlight cells" with "Use formula to determine which cells to format". You can then write any formula, and the cell is formatted if the formula evaluates to TRUE.

Is any of these acceptable?

J.Ty.