Checking a single cell for multiple values

Hi,
This is my first post, so apologies if I miss anything or what I'm after isn't very clear.

I'm trying to validate the data entered into a series of cells each cell can contain a different set of data but the value N/A is also permitted. For example:

Cell A1 could contain a date from 2013-01-01 thru 2013-12-01 but the value N/A is also valid
Cell A2 could contain a decimal from 0.01 thru 302502.23 but the value N/A is also valid
Cell A3 could contain an integer from 3 thru to 9000 but the value N/A is also valid

When the acceptable values are entered then I want to be able to carry on otherwise I want to pop up with an error.

Hope that makes sense and any help much appreciated.

Perfect, many thanks this works an absolute treat

Hi,
In addition to the previous request around the validation my user has now come back with something else. If a user enters a decimal into a field that is only expecting an integer then they want it to error rather than rounding as it does currently.

Is there a way I can do this and also incorporate the validation above.

Thanks in advance

Originally Posted by xxchurch

Hi,
In addition to the previous request around the validation my user has now come back with something else. If a user enters a decimal into a field that is only expecting an integer then they want it to error rather than rounding as it does currently.

Is there a way I can do this and also incorporate the validation above.

Thanks in advance

I managed to sort out the above issue with the following:

=OR(AND(C47>=0,C47<=900,INT(C47)=C47),C47="N/A")

However, I now have another variation to cater for. I need to be able to check that where only 2 decimal places are allowed in a cell that the user has not entered more than the 2. This is in addition to also being able to check the upper and lower boundaries and the presence of N/A too.

Really appreciate any help.

Thanks

I think I have the only integers working as per the code in my earlier post. The problem I'm having is combining checking upper and lower boundaries, with N/A and also being able to validate that only 2 decimal places have been entered.

Originally Posted by hemesh

for only integers you could use in A3

=OR(AND(A3>=3,A3<=9000,SUM(--(ISNUMBER(SEARCH(".",A3))))=0),A3="N/A")

for decimal criteria use in A2
=OR(AND(A2>=0.01,A2<=302502.23,LEN(A2)-SEARCH(".",A2)=2),A2="N/A")

Thanks for the above response. the integer check is working perfectly, however the decimal check is not recognising N/A as a valid entry.

Any ideas?

Thanks

Originally Posted by hemesh

Hello xxchurch try this =OR(AND(A2>=0.01,A2<=302502.23,ISNUMBER(LEN(A2)-SEARCH(".",A2)=2)),A2="N/A")

Hi,
Really appreciate you taking the time to help me out. I can't get the example above working at all, the N/A validation works but the decimal place and boundary checking just errors each time even when a valid number is entered. The example below:

=OR(AND(A2>=0.01,A2<=302502.23,LEN(A2)-SEARCH(".",A2)=2),A2="N/A")

was doing all of the boundary and decimal place checking perfectly but the N/A check wasn't working, so it was really close. Does it make a difference if I have the cell set as General or Number?

Thanks in advance