Log In
Register
Home
Active Member
October 17, 2018
Offline
Hi, I have created a data validation to stop entering certain unacceptable values. The formula has only 120 characters, yet the data validation does not work.
At a certain point it was working but hen i made a final correction it stopped doing so, and I have not idea why.
I tried also to recreate but it does not work.
Any suggestion what I am doing wrong?
I attach the file.
Thank you
Trusted Members
October 18, 2018
Offline
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.
Active Member
October 17, 2018
Offline
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.
VIP
Trusted Members
June 25, 2016
Offline
Hi Annamaria
Try unticking the Ignore Blank option in your DV.
Sunny
New Member
October 18, 2018
Offline
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?
Active Member
October 17, 2018
Offline
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.
VIP
Trusted Members
June 25, 2016
Offline
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
Active Member
October 17, 2018
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
1 Guest(s)
