Validation Formula
 

Help

We are building a schedule using excel. I can not seem to program an
allowable shift amount per day. The goal is to have the table with ROW 37
only allowing 3 units. And ROW 36 only allowing 1 and 35 allows 2.

The validation seems to be fine in row 37, but when it comes to row 35 and
36 we cant seem to have restricted.

Thanks

Scheduler Hell Whole

Validation Formula
 

What have you entered in the validation?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"cathyds1" wrote in message
...
Help

We are building a schedule using excel. I can not seem to program an
allowable shift amount per day. The goal is to have the table with ROW 37
only allowing 3 units. And ROW 36 only allowing 1 and 35 allows 2.

The validation seems to be fine in row 37, but when it comes to row 35 and
36 we cant seem to have restricted.

Thanks

Scheduler Hell Whole

Validation Formula
 

My current validation is: =(B$37)<3
Intially I used <=3, and found that the staff could force me to 4. So I
changed it to just <. Now if they tab or arrow to next cell they are not
allowed to, but if they cursor to the next cell it is accepted. (Any way
around this?) I alos have discovered they can copy paste to force me to 4.
(My company uses Excel 2000).
The question is: when I try to write a formula which limits B$37<3, B$35
limited to 2, and B$36 limited to 1, every attempt I've made restricts B$37
to 2. Or returns an error message. Any help for me? Thanks alot. Cathy

"Bob Phillips" wrote:

What have you entered in the validation?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"cathyds1" wrote in message
...
Help

We are building a schedule using excel. I can not seem to program an
allowable shift amount per day. The goal is to have the table with ROW 37
only allowing 3 units. And ROW 36 only allowing 1 and 35 allows 2.

The validation seems to be fine in row 37, but when it comes to row 35 and
36 we cant seem to have restricted.

Thanks

Scheduler Hell Whole

Validation Formula
 

I can't reproduce your problem, but a couple of points:

- the paste thing is a problem, can't overcome that unless you use event VBA
code behind the cell
- testing for <=3 will allow any value <=3, negatives and fractions, so
maybe you should do between whole numbers

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"cathyds1" wrote in message
...
My current validation is: =(B$37)<3
Intially I used <=3, and found that the staff could force me to 4. So I
changed it to just <. Now if they tab or arrow to next cell they are not
allowed to, but if they cursor to the next cell it is accepted. (Any way
around this?) I alos have discovered they can copy paste to force me to
4.
(My company uses Excel 2000).
The question is: when I try to write a formula which limits B$37<3, B$35
limited to 2, and B$36 limited to 1, every attempt I've made restricts
B$37
to 2. Or returns an error message. Any help for me? Thanks alot. Cathy

"Bob Phillips" wrote:

What have you entered in the validation?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"cathyds1" wrote in message
...
Help

We are building a schedule using excel. I can not seem to program an
allowable shift amount per day. The goal is to have the table with ROW
37
only allowing 3 units. And ROW 36 only allowing 1 and 35 allows 2.

The validation seems to be fine in row 37, but when it comes to row 35
and
36 we cant seem to have restricted.

Thanks

Scheduler Hell Whole

Validation Formula
 

Bob, the staff enter "D" or "D+" which designates the hours of their shift. I
tried the between whole numbers idea and couldn't enter anything without
getting my error message. Maybe I should mention that row 35 is a COUNTIF
formula counting the "D"s and the same with row 36 counting the "D+"s in the
columns above. Then row 37 is the sum of 35 and 36. Any ideas to restrict 35
to 2 and 36 to 1 and 37 to 3? Thank you for your time. Cathy

"Bob Phillips" wrote:

I can't reproduce your problem, but a couple of points:

- the paste thing is a problem, can't overcome that unless you use event VBA
code behind the cell
- testing for <=3 will allow any value <=3, negatives and fractions, so
maybe you should do between whole numbers

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"cathyds1" wrote in message
...
My current validation is: =(B$37)<3
Intially I used <=3, and found that the staff could force me to 4. So I
changed it to just <. Now if they tab or arrow to next cell they are not
allowed to, but if they cursor to the next cell it is accepted. (Any way
around this?) I alos have discovered they can copy paste to force me to
4.
(My company uses Excel 2000).
The question is: when I try to write a formula which limits B$37<3, B$35
limited to 2, and B$36 limited to 1, every attempt I've made restricts
B$37
to 2. Or returns an error message. Any help for me? Thanks alot. Cathy

"Bob Phillips" wrote:

What have you entered in the validation?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"cathyds1" wrote in message
...
Help

We are building a schedule using excel. I can not seem to program an
allowable shift amount per day. The goal is to have the table with ROW
37
only allowing 3 units. And ROW 36 only allowing 1 and 35 allows 2.

The validation seems to be fine in row 37, but when it comes to row 35
and
36 we cant seem to have restricted.

Thanks

Scheduler Hell Whole

Validation Formula
 

A little more info. I have tried: =AND(B$35<2,B$36<1,B$37<3); also tried
same formula with OR.
The AND holds B37 to 2, the OR allows them to force B37 to 4. Cathy
"Bob Phillips" wrote:

I can't reproduce your problem, but a couple of points:

- the paste thing is a problem, can't overcome that unless you use event VBA
code behind the cell
- testing for <=3 will allow any value <=3, negatives and fractions, so
maybe you should do between whole numbers

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"cathyds1" wrote in message
...
My current validation is: =(B$37)<3
Intially I used <=3, and found that the staff could force me to 4. So I
changed it to just <. Now if they tab or arrow to next cell they are not
allowed to, but if they cursor to the next cell it is accepted. (Any way
around this?) I alos have discovered they can copy paste to force me to
4.
(My company uses Excel 2000).
The question is: when I try to write a formula which limits B$37<3, B$35
limited to 2, and B$36 limited to 1, every attempt I've made restricts
B$37
to 2. Or returns an error message. Any help for me? Thanks alot. Cathy

"Bob Phillips" wrote:

What have you entered in the validation?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"cathyds1" wrote in message
...
Help

We are building a schedule using excel. I can not seem to program an
allowable shift amount per day. The goal is to have the table with ROW
37
only allowing 3 units. And ROW 36 only allowing 1 and 35 allows 2.

The validation seems to be fine in row 37, but when it comes to row 35
and
36 we cant seem to have restricted.

Thanks

Scheduler Hell Whole