Data validation: change cell style instead of error message

English
#1

Hey all,

I know that I can set data validation for a cell, for example to ensure the user of my sheet properly input a date format.

Now the “default” behavior is to display an error dialog when data is not valid.

Instead of that, I would like to change the style of the sheet, so the user can quickly, as an eye glance, know which cell has bad data.

It is also very useful when a user copy/paste a huge set of data.

Do you know how to achieve that?

I tried to use the “formula” mode in the style section, but I didn’t found a way to reference the result of the validation.

Thanks for your lights!

#2

A normal copy/paste will overwrite the Data Validity settings and the Conditional Format settings.
You must use the “paste as unformatted text” option.

If you want to check the results of the feature number recognition, then you can use the Conditional Format and the function ISNUMBER()

#3

Hi, ans thanks for your reply.

Unfortunately it does not really solve my problem.

  1. I want the style of my cell to CHANGE if the data are not valid.

  2. I also talked about the copy paste because: if you copy let’s say 100 entries with some that are invalid, you will get message box errors, but you won’t be able to identify which one is bad. It is why the bullet 1. is helpful, because it gives a visual feedback on which cell is invalid.

Thanks!

#4

Please upload an ODF type sample file with some valid and invalid data examples.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.