Data Validation

steve_d

New Member
Joined
Jun 18, 2002
Messages
2
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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

Active Member
Joined
Apr 14, 2002
Messages
384
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.
 

Forum statistics

Threads
1,144,359
Messages
5,723,893
Members
422,524
Latest member
wirkkarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top