LinkBack URL
About LinkBacks
I need to add data validation to a cell in Excel 2003. The cell should accept any whole number from 0 to 500,000 or "NA" for not applicable. Can anyone show me how to do this?
I need to add data validation to a cell in Excel 2003. The cell should accept any whole number from 0 to 500,000 or "NA" for not applicable. Can anyone show me how to do this?
Hi BP05, and welcome to the forum.
Try setting the Data Validation for that cell to Custom, and use the following formula (change G1 to your actual cell being referenced):
=OR(G1="NA",IF(ISNUMBER(G1),AND(INT(G1)-G1=0,G1>=0,G1<=500000),FALSE))
Paul, this is wonderful, but my manager now wants it to accept decimal values. Is there a way to get it to accept a whole number and/or a decimal? Thanks!
You should be able to just remove the INT test, e.g.
=OR(G1="NA",IF(ISNUMBER(G1),AND(INT(G1)-G1=0,G1>=0,G1<=500000),FALSE))
becomes
=OR(G1="NA",IF(ISNUMBER(G1),AND(G1>=0,G1<=500000),FALSE))
Actually, after thinking for a second, you should just be able to use:
=OR(A1="NA",AND(ISNUMBER(A1),A1>=0,A1<=500000))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks