Restricted values in cells

[mns]

Hi,
I want to make a rule for a sheet which gives an alert when a user enters
"," comma in the cell while typing 12,34. I tried Data Varification part
couldnt figure out how to make this happen. If anyone knows how to restrict
spesific character I would be happy to hear it.

Thanx

 

[ShaneDevenshire]

Hi,

In cell C1 I entered a comma, highlight the range you want to apply the
validation to and then In the data validation choose Custom and enter

=ISERROR(FIND($C$1,A1))

where A1 is the first cell of the data validation range.

 

[ShaneDevenshire]

Hi again,

If you want to apply this to the entire sheet, move to another sheet and
enter a comma in a cell. Then name the cell. In my example I named the cell
comma. Now move back to the sheet you want to restrict. Select the entire
sheet and enter the following formula in the Data Validation, Custom:

=ISERROR(FIND(Comma,A1))

 

[Gord Dibben]

Alternative to comma in a reference cell.

=ISERROR(FIND(CHAR(44),A1))


Gord Dibben MS Excel MVP

 

[mns]

Thank you! The first one worked but the thing is I need to make sure from
all sheet. So I tried the second option. I renamed the second sheet as Comma,
like you did. Then I came to my first sheet and selected a range to
test(A1:A2 + B1:B2).
then tried your formula
=ISERROR(FIND(Comma,A1))

which ended with an error saying;
"A named range you specified cannot be found."

 

[Gord Dibben]

Shane said to name the cell with the comma as "Comma"

Insert>Name>Define

Do not name the sheet as "Comma"

BTW..........have you tried my formula?


Gord

 

[mns]

Yes actually, Char(44) made it easy thank you a lot. But now guess what, I
wonder what is the char number of dot, question mark.

Thank you so much for saving me time.

p.s.:This topic is not marked as done. I am using web browser and couldnt
find an option to say "my problem has been solved". If I am missing that pls
let me note tat too

 

[Gord Dibben]

In top cell of an empty column enter =CHAR(ROW())

Copy down to row 255 to see the ascii characters associated with each row
number.


Gord