Combine 2 Data Validations Q
 

I have the following Data Validation in a cell using the "Custom" validation

=J10<""

This only allows input if there is a value in J10. How would I also include
within this validation a requirement for the user to enter only values
(which may include decimal places)? The problem I am encountering at the
moment is that some users are hitting the space bar to change/remove figures
and this is causing problems within a separate export sheet I have, as it
interprets the values entered as "Space" although visually it looks blank

Thanks

Combine 2 Data Validations Q
 

How about

=AND(J10<"",ISNUMBER(J10))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John" wrote in message
...
I have the following Data Validation in a cell using the "Custom"
validation

=J10<""

This only allows input if there is a value in J10. How would I also
include
within this validation a requirement for the user to enter only values
(which may include decimal places)? The problem I am encountering at the
moment is that some users are hitting the space bar to change/remove
figures
and this is causing problems within a separate export sheet I have, as it
interprets the values entered as "Space" although visually it looks blank

Thanks

Combine 2 Data Validations Q
 

Thanks Bob, not quite what I require in that if I hit the space bar to
remove the input value it leaves ' in the cell and that causes me problems,
I thought by having 2 validations it would work

Some users are clearing the cell via space bar, instead of the delete key,
or clear contents



"Bob Phillips" wrote in message
...
How about

=AND(J10<"",ISNUMBER(J10))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John" wrote in message
...
I have the following Data Validation in a cell using the "Custom"
validation

=J10<""

This only allows input if there is a value in J10. How would I also
include
within this validation a requirement for the user to enter only values
(which may include decimal places)? The problem I am encountering at the
moment is that some users are hitting the space bar to change/remove
figures
and this is causing problems within a separate export sheet I have, as it
interprets the values entered as "Space" although visually it looks blank

Thanks

Combine 2 Data Validations Q
 

Don't know what you mean John, if I hit the spacebar I get an error.

I do get the ability to hit backspace and clear it, but that can also be
trapped with

=AND(ISNUMBER(J10),LEN(trim(J10))0)

and uncheck Ignore Blank, but this does not force some entry.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John" wrote in message
...
Thanks Bob, not quite what I require in that if I hit the space bar to
remove the input value it leaves ' in the cell and that causes me
problems,
I thought by having 2 validations it would work

Some users are clearing the cell via space bar, instead of the delete key,
or clear contents



"Bob Phillips" wrote in message
...
How about

=AND(J10<"",ISNUMBER(J10))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John" wrote in message
...
I have the following Data Validation in a cell using the "Custom"
validation

=J10<""

This only allows input if there is a value in J10. How would I also
include
within this validation a requirement for the user to enter only values
(which may include decimal places)? The problem I am encountering at
the
moment is that some users are hitting the space bar to change/remove
figures
and this is causing problems within a separate export sheet I have, as
it
interprets the values entered as "Space" although visually it looks
blank

Thanks

Combine 2 Data Validations Q
 

Bob, in another sheet (Import) I'm simply using a =Sheet1!A5 reference. This
normally returns Zero if the cell in Sheet1!A5 is blank, or the value in
that cell if the user inputs a value. But if the user clears the value in
Sheet1!A5 with the spacebar, this leaves a " ' " value in Sheet1!A5. On my
Import sheet the value then returns a <blank, this is causing a problem
when I export this cell (Import!A1) to Access. I've tried a
If(Sheet1!A5=',0) but thats not valid. I need a value in Import!A1 not " ' "









"Bob Phillips" wrote in message
...
Don't know what you mean John, if I hit the spacebar I get an error.

I do get the ability to hit backspace and clear it, but that can also be
trapped with

=AND(ISNUMBER(J10),LEN(trim(J10))0)

and uncheck Ignore Blank, but this does not force some entry.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John" wrote in message
...
Thanks Bob, not quite what I require in that if I hit the space bar to
remove the input value it leaves ' in the cell and that causes me
problems,
I thought by having 2 validations it would work

Some users are clearing the cell via space bar, instead of the delete
key,
or clear contents



"Bob Phillips" wrote in message
...
How about

=AND(J10<"",ISNUMBER(J10))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John" wrote in message
...
I have the following Data Validation in a cell using the "Custom"
validation

=J10<""

This only allows input if there is a value in J10. How would I also
include
within this validation a requirement for the user to enter only values
(which may include decimal places)? The problem I am encountering at
the
moment is that some users are hitting the space bar to change/remove
figures
and this is causing problems within a separate export sheet I have, as
it
interprets the values entered as "Space" although visually it looks
blank

Thanks

Combine 2 Data Validations Q
 

John try naming Sheet!A5 (InsertNameDefine...) and using the name.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John" wrote in message
...
Bob, in another sheet (Import) I'm simply using a =Sheet1!A5 reference.
This
normally returns Zero if the cell in Sheet1!A5 is blank, or the value in
that cell if the user inputs a value. But if the user clears the value in
Sheet1!A5 with the spacebar, this leaves a " ' " value in Sheet1!A5. On my
Import sheet the value then returns a <blank, this is causing a problem
when I export this cell (Import!A1) to Access. I've tried a
If(Sheet1!A5=',0) but thats not valid. I need a value in Import!A1 not " '
"









"Bob Phillips" wrote in message
...
Don't know what you mean John, if I hit the spacebar I get an error.

I do get the ability to hit backspace and clear it, but that can also be
trapped with

=AND(ISNUMBER(J10),LEN(trim(J10))0)

and uncheck Ignore Blank, but this does not force some entry.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John" wrote in message
...
Thanks Bob, not quite what I require in that if I hit the space bar to
remove the input value it leaves ' in the cell and that causes me
problems,
I thought by having 2 validations it would work

Some users are clearing the cell via space bar, instead of the delete
key,
or clear contents



"Bob Phillips" wrote in message
...
How about

=AND(J10<"",ISNUMBER(J10))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John" wrote in message
...
I have the following Data Validation in a cell using the "Custom"
validation

=J10<""

This only allows input if there is a value in J10. How would I also
include
within this validation a requirement for the user to enter only
values
(which may include decimal places)? The problem I am encountering at
the
moment is that some users are hitting the space bar to change/remove
figures
and this is causing problems within a separate export sheet I have,
as
it
interprets the values entered as "Space" although visually it looks
blank

Thanks