Allow only nummeric values

[sapai]

Hi,

I have a requirement wherein I need to format the sheet to accept only
numerical values. Any other data (numerical or special characters) should be
changed to "N/A". is this possible in Excel ?

Thx
SPA

 

[sapai]

I mean any other data <Alphabetic or special characters>..

 

[Bill Kuunders]

one way
use <data> <validation>
allow decimal
greater than
enter a value something like -1,000,000,000

 

[sapai]

Thanks Bill,

I also have another requirement wherein invalid data is to be replaced by
"N/A". is this possible having already avoided alpha?

 

[Rick Rothstein]

Describe "numerical" for us. Obviously all digits meet that criteria. What
about numbers with a decimal point in them? Or a leading plus and/or minus
sign. How about with thousand's separators. Excel recognizes 1.23E+45 as
number... is this acceptable to you too? What about numbers with leading
currency symbols or trailing % symbols? Or any other variations that I might
be forgetting at the moment?

 

[sapai]

You are right Rick.

I was too generic in my statement. What i am actually looking at is
percentage values. user may enter from 1 to 100. I am not looking at any
decimal values.

- SPA

 

[Rick Rothstein]

I'm sorry, but I have a couple of follow up questions for you. What do you
want to happen if a floating point value is entered between 1 and 100 (such
as 12.3)? Do you want it rounded to the nearest integer or flagged "N/A"? Do
you want to preserve the errant value typed in, but just display it as "N/A"
(in other words, show "N/A" in the cell, but keep the value in the Formula
Bar) or is it okay to physically replace the errant value with "N/A" (thus
losing it completely)? Is a VB solution permissible?

How close we can come to what you want will depend on how you answer all of
the questions above (please don't skip any). The VB solution will allow us
to come the closest to whatever you end up specifying... a non-VB solution
will require some compromises.