Lock cell if row already has "V" as a value

[jessicarabelli]

Hello,

I have created a corporate calendar where the rows contain the days of the month and the columns contain the employee's name (14 employees total) and the cells may contain V for vacation when the employee will be out of the office. I do not want more than one employee to take vacation at the same time.


I would like to block a cell if the value "V" has already been entered in that specific day. How can I do that?

PS: I am creating this calendar per the request of my manager, not my idea to block employees from taking vacation on the same day.

 

[Scott T]

You can use data validation for this

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

day

Emp1

Emp2

Emp3

Emp4

Emp5

Emp6

Emp7

Emp8

Emp9

Emp10

Emp11

Emp12

Emp13

Emp14

2

1

v

3

2

4

3

5

4

v

6

5

7

6

8

7

<tbody>
</tbody>

Select the cells for example B2:O32
in data validation select custom and use this formula

=COUNTIF($B2:$O2,"v")<=1

If someone tries to put more then on V on a row in the selected range an error box will pop up.
You can customize the error box in the Error Alert tab of data validation.

 

[jessicarabelli]

You can use data validation for this

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

day

Emp1

Emp2

Emp3

Emp4

Emp5

Emp6

Emp7

Emp8

Emp9

Emp10

Emp11

Emp12

Emp13

Emp14

2

1

v

3

2

4

3

5

4

v

6

5

7

6

8

7

<tbody>
</tbody>

Select the cells for example B2:O32
in data validation select custom and use this formula

=COUNTIF($B2:$O2,"v")<=1

If someone tries to put more then on V on a row in the selected range an error box will pop up.
You can customize the error box in the Error Alert tab of data validation.

That works, thank you. But is there a way for me to apply this validation to all of the table instead of manually entering it for each row?

Thank you so much.

 

[Scott T]

Just select all the rows and apply the validation. DV will change the row number as it moves down.

 

[jessicarabelli]

Ok, so now I have a different, MORE COMPLICATED situation. Employees cannot take vacation if their deputy is on vacation -sometimes you will have a person being deputy to two different employees or in case of EMPLOYEE 7(COLUMN H), EVERYONE is his deputy so no one can take vacation if he selects vacation.

I cannot reorganize the columns because they are in separated by department...

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

1

Emp1

Emp2

Emp3

Emp4

Emp5

Emp6

Emp7

Emp8

Emp9

Emp10

Emp11

Emp12

Emp13

Emp14

Emp15

2

day/deputy

(Emp2)

(E1)

(E6)

(E5)

(E4)

(E3)

Everyone

(E9)

(E8)

(E11)

(E10)

(E13)

(E15)

(E3)

(E13)

3

1

4

2

5

3

v

6

4

7

5

8

6

<tbody>
</tbody>

PLEASE HELP?

 

[Rick Rothstein]

Ok, so now I have a different, MORE COMPLICATED situation. Employees cannot take vacation if their deputy is on vacation -sometimes you will have a person being deputy to two different employees or in case of EMPLOYEE 7(COLUMN H), EVERYONE is his deputy so no one can take vacation if he selects vacation.

I don't understand how this would affect anything. If only one employee per day can be on vacation, what does it matter if that other employee is a deputy, boss or whatever... if he is off, you can't be.

 

[jessicarabelli]

I don't understand how this would affect anything. If only one employee per day can be on vacation, what does it matter if that other employee is a deputy, boss or whatever... if he is off, you can't be.

Rick, sorry for the confusion. Now we forget about the first rule. We can have MULTIPLE people on vacation, as long as we have either the employee or deputy present. For example, I can have employee 1,3 and 4 on vacation. But I cannot have employee 3 and 6 on vacation at the same time since one of them needs to be in the office to oversee the work.

I believe I will have to right different rules for each set, but am not sure how to do so. Or a huge formula that captures it all.

 

[Scott T]

I think VBA may be the best way to do this.

But I cannot have employee 3 and 6 on vacation at the same time since one of them needs to be in the office to oversee the work.

Since employee 3 or 6 must be in the office I assume that the revers is true and employee 6 should not be allow to take vacation if employee 3 has already requested vacation for that day?

 

[jessicarabelli]

I think VBA may be the best way to do this.



Since employee 3 or 6 must be in the office I assume that the revers is true and employee 6 should not be allow to take vacation if employee 3 has already requested vacation for that day?

Scott, that is correct.

Employee 1 cannot take vacation if employee 2 has requested vacation (and vice versa)
Employee 3 cannot take vacation if employee 6 has requested vacation (and vice versa)
Employee 4 cannot take vacation if employee 5 has requested vacation (and vice versa)
No one can take vacation if employee 7 has requested vacation
Employee 8 cannot take vacation if employee 9 has requested vacation (and vice versa)
Employee 10 cannot take vacation if employee 11 has requested vacation (and vice versa)
Employee 12 cannot take vacation if employee 13 has requested vacation (and vice versa)
Employee 13 cannot take vacation if employee 15 has requested vacation (and vice versa)
Employee 14 cannot take vacation if employee 3 has requested vacation (and vice versa)
Employee 15 cannot take vacation if employee 13 has requested vacation (and vice versa)

I am very unsure of how I should do this. I tried the =countif()<=1 that you suggested first by selecting only the two roles at play. But on Employee 3 and 6 I already had a problem since the columns are not next to each other...haha And some columns will appear twice in a rule (ie: employee 13 is the deputy for 15 AND 12 so this employee cannot go on vacation if one of the other two have already requested vacation for that specific date).

 

[Scott T]

So If employee 7 can not take vacation if anyone has already requested vacation?