sparky2205
Active Member
- Joined
- Feb 6, 2013
- Messages
- 481
- Office Version
- 365
- 2016
- Platform
- Windows
I am trying to use Data Validation to achieve the following:
Allow n/a and a valid decimal value
Prevent text other than n/a and errors like; .23.23 where the user accidentally enters a second decimal point
I have the following formula which works fine when I use it directly on a spreadsheet:
i.e. n/a returns FALSE, 3.51 returns FALSE, .23.23 returns TRUE
However, when I transfer this to a Data Validation rule:
n/a triggers the rule, 3.51 triggers the rule, .23.23 doesn't trigger the rule. The exact opposite behaviour to that directly in the spreadsheet and to what I need.
Any thoughts?
Allow n/a and a valid decimal value
Prevent text other than n/a and errors like; .23.23 where the user accidentally enters a second decimal point
I have the following formula which works fine when I use it directly on a spreadsheet:
Excel Formula:
=AND(ISERROR(FIND("N/A",UPPER(B9))),((LEN(B9)-LEN(SUBSTITUTE(B9,".","")))<>1))
However, when I transfer this to a Data Validation rule:
n/a triggers the rule, 3.51 triggers the rule, .23.23 doesn't trigger the rule. The exact opposite behaviour to that directly in the spreadsheet and to what I need.
Any thoughts?