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

J

jessicarabelli

New Member
Joined
Aug 11, 2017
Messages
11
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
You can use data validation for this
[TABLE="class: grid, width: 979"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]day
[/TD]
[TD]Emp1
[/TD]
[TD]Emp2
[/TD]
[TD]Emp3
[/TD]
[TD]Emp4
[/TD]
[TD]Emp5
[/TD]
[TD]Emp6
[/TD]
[TD]Emp7
[/TD]
[TD]Emp8
[/TD]
[TD]Emp9
[/TD]
[TD]Emp10
[/TD]
[TD]Emp11
[/TD]
[TD]Emp12
[/TD]
[TD]Emp13
[/TD]
[TD]Emp14
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]v
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD="align: right"]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD="align: right"]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD="align: right"]4
[/TD]
[TD][/TD]
[TD]v
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[TD="align: right"]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7
[/TD]
[TD="align: right"]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8
[/TD]
[TD="align: right"]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Select the cells for example B2:O32
in data validation select custom and use this formula
Code: 
=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.
 
Last edited:
Upvote 0
Scott T said:
You can use data validation for this
[TABLE="class: grid, width: 979"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]day
[/TD]
[TD]Emp1
[/TD]
[TD]Emp2
[/TD]
[TD]Emp3
[/TD]
[TD]Emp4
[/TD]
[TD]Emp5
[/TD]
[TD]Emp6
[/TD]
[TD]Emp7
[/TD]
[TD]Emp8
[/TD]
[TD]Emp9
[/TD]
[TD]Emp10
[/TD]
[TD]Emp11
[/TD]
[TD]Emp12
[/TD]
[TD]Emp13
[/TD]
[TD]Emp14
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]v
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD="align: right"]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD="align: right"]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD="align: right"]4
[/TD]
[TD][/TD]
[TD]v
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[TD="align: right"]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7
[/TD]
[TD="align: right"]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8
[/TD]
[TD="align: right"]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Select the cells for example B2:O32
in data validation select custom and use this formula
Code: 
=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.
Click to expand...

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.
 
Upvote 0
Just select all the rows and apply the validation. DV will change the row number as it moves down.
 
Upvote 0
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...:(
[TABLE="class: grid, width: 979"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]Emp1
[/TD]
[TD]Emp2
[/TD]
[TD]Emp3
[/TD]
[TD]Emp4
[/TD]
[TD]Emp5
[/TD]
[TD]Emp6
[/TD]
[TD]Emp7
[/TD]
[TD]Emp8
[/TD]
[TD]Emp9
[/TD]
[TD]Emp10
[/TD]
[TD]Emp11
[/TD]
[TD]Emp12
[/TD]
[TD]Emp13
[/TD]
[TD]Emp14
[/TD]
[TD]Emp15
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD="align: right"]day/deputy
[/TD]
[TD](Emp2)
[/TD]
[TD](E1)
[/TD]
[TD](E6)
[/TD]
[TD](E5)
[/TD]
[TD](E4)
[/TD]
[TD](E3)
[/TD]
[TD]Everyone
[/TD]
[TD](E9)
[/TD]
[TD](E8)
[/TD]
[TD](E11)
[/TD]
[TD](E10)
[/TD]
[TD](E13)
[/TD]
[TD](E15)
[/TD]
[TD](E3)
[/TD]
[TD](E13)
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD="align: right"]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD="align: right"]3
[/TD]
[TD][/TD]
[TD]v
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[TD="align: right"]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7
[/TD]
[TD="align: right"]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8
[/TD]
[TD="align: right"]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

PLEASE HELP?:confused:
 
Upvote 0
jessicarabelli said:
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.
Click to expand...
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.
 
Upvote 0
Rick Rothstein said:
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.
Click to expand...

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.
 
Upvote 0
I think VBA may be the best way to do this.

jessicarabelli said:
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.
Click to expand...

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?
 
Upvote 0
Scott T said:
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?
Click to expand...

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).
 
Upvote 0
So If employee 7 can not take vacation if anyone has already requested vacation?
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Print handouts for multiple employees that use different values
Replies
3
Views
353
jolivanes
jolivanes
I
  • Question Question
Tracking vacation requests
Replies
9
Views
919
KRice
K
P
  • Question Question
If XLOOKUP value is blank, return blank
Replies
1
Views
345
DanteAmor
DanteAmor
S
  • Question Question
Automatic accrual calculations by day with accrual rate?
Replies
0
Views
693
Sunshine8790
S
A
  • Question Question
Assign Rows of Data to Employees from Master
Replies
9
Views
484
mumps
mumps

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top