Data Validation

[steve_d]

Hi
I'm trying to make sure all data enterd into a column is validated, but when data is pasted in rather than typed it ignores validation, is there a way to make sure pasted data is also validated (currently using custom queries in the standard excel 97 validation option).

Thanks
Steve

 

[Mudface]

Sorry, I don't think there's a way around validating pasted data. You can highlight invalid data by using the Audit tools, however (click on Tools- Auditing- Show Audit Toolbar and then choose the 'Circle Invalid Data' button).

 

[s-o-s]

On 2002-08-30 05:24, steve_d wrote:
Hi
I'm trying to make sure all data enterd into a column is validated, but when data is pasted in rather than typed it ignores validation, is there a way to make sure pasted data is also validated (currently using custom queries in the standard excel 97 validation option).

Thanks
Steve

Standard Copying and pasting overwrites the validation rules.

What you can do is paste special formulas or paste special values...This allows you to put your new values in without overwriting the validation, this does however allow invalid data to be entered...So then hit the Circle Invalid Data button on the Auditing Toolbar it will at least highlight(circle) all the cells that do not meet the criteria.

Not the ideal but at least you get somehelp.

Or

Using VB you can program worksheet change events to test the data that is copied in.

Hope this helps.
Sean.

 

[lenze]

See this thread:

http://www.mrexcel.com/board/viewtopic.php?topic=15008&forum=2

Note particularlly Tom's approach