Looked at your submission, but am unable to determine where the data validation is located.  You have many columns of data.  Please be more specific in your request for help.  Tell us specifically where your issue is and what is happening and what is not happening that you want to happen.

You can see at F5 Go to special --> Data validation. It is in the range AM8:BX4000

The formula I used is =IF(ISNUMBER(AM8);IF(AM8>=AM$4;TRUE;FALSE);IF(OR(AM8="x";AM8="xE";AM8="E";AM8="-";AM8=AM$3601;AM8="<"&AM$4);TRUE;FALSE))

if I put the formula in a cell it works, so there is not a problem with the formula. In the dialog box of Data Validation there is no sign that something is wrong and not accepted, still it doesn't work.

If I check the data validation of any of the cells it is there, still it doesn't work, still it accept values I defined not to accept.

I don't find the problem.

Hi Annamaria

Try unticking the Ignore Blank option in your DV.

Sunny

Hi.

I put this into the data validation and it worked.

=(ISNUMBER(AM8)) * (AM8>AM$4)+((AM8="x")+(AM8="xE")+(AM8="E")+(AM8="-"))

The * is equivalent to AND and the + is equivalent to OR.  So the statement reads:

if AM8 is a number and it's > AM4 OR AM8 is one of x, xE, E, - then the value is ok.  I didn't get your last two criteria there - is AM$3601 supposed to be a number value?  There is nothing in that cell for me to determine that.  Also on your last criteria (AM8="<"&AM$4 - what are you trying to do there?  What is that ampersand for?

Hi,

Sorry for answering late, I had to deal with other things...

Thank you for all answers, both versions worked (unticking the ignore blank option or the formula proposed by Janice).

However I still have a question:

With my last condition AM8="<"&AM$4

I wanted to regulate that users can write that the value is less than the limit of quantitation of the method (the limit is given in row4) only in a specific format.

For ex if the limit is 0,2, it is only allowed to write <0,2 and not 0,20

It works, but it doesn't work in case of integers, if the limit is 5,0 (written so in row4), it accepts to be written <5 but I would need to create a condition that accepts <5,0.

Is that possible?

with AM$3601 the condition I is that sometimes there is a template formula in that row and users should be able to copy paste that formula in a given row above.

Hi Annamaria

You can try using CELL("format",X) where X is the cell to check the format.

The formula will return F1 (1 decimal place) and  F2 (2 decimal places) etc

You will need to format the cell X first to the required format.

Hope this helps.

Sunny

Hi Sunny,

Thank you for the tip, but unfortunately it doesn't work, because I need the condition for the "<5,0", and that is interpreted as text not as number.

But the 5,0 is variable, at every column it is different. That is why I used the AM8="<"&AM$4 formula, but this does not allow me to use one decimal in case it is an integral value...

I was thinking to use instead of AM$4 the formula fixed(AM$4,1), but it is not OK, for it doesn't match all my columns, in some cases I need two decimals or 3...

Annamaria

Hi Annamaria

I think I misunderstood your original question as I thought you wanted to check on the format..

"I wanted to regulate that users can write that the value is less than the limit of quantitation of the method (the limit is given in row4) only in a specific format."

Are you saying that users can enter <5.0 (with the < symbol) into cell AM8 and it should be validated as TRUE if the value portion (5.0) is the same as AM4?

Even the decimal place must match i.e. if AM4 has 5.0 then user cannot enter <5.00 in AM8 but only <5.0?

The formatting part with decimal is very tricky. That is why I suggested using CELL() to check on the format of cell AM4 to determine the decimal placing.

I hope I got it correct this time. I will try and see what I can do.

Sunny

Hi Annamaria

Try this. You can then incorporate this formula into your actual DV.

You will first need to format the rows 4 of your actual data to the correct decimal place for this to work.

If you change the cell format you will need to press F9 for the CELL function to recalculate.

Hope this helps.

Sunny

What I am doing is to convert the values in row 4 to text so I need to determine the number of decimal place formatted in that cell.

I will then format using TEXT to the correct decimal place. So it is very important that you format the cells in row 4 to the required format.

You can use my example to check for any number of decimal place. It will show F0, F1, F2 etc.

Don't forget to press F9 to update the formula if you change the cell format.

If you have already predetermined the decimal place for each cell in row 4 then you don't need to calculate the decimal place. Just use TEXT with the correct decimal place format.

Sunny