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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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

A
  • Question Question
Automate Attendance Marking Based on Daily Schedule in Excel
Replies
1
Views
273
Peter_SSs
Peter_SSs
R
  • Question Question
Print handouts for multiple employees that use different values
Replies
3
Views
360
jolivanes
jolivanes
I
  • Question Question
Tracking vacation requests
Replies
9
Views
995
KRice
K
P
  • Question Question
If XLOOKUP value is blank, return blank
Replies
1
Views
678
DanteAmor
DanteAmor
S
  • Question Question
Automatic accrual calculations by day with accrual rate?
Replies
0
Views
738
Sunshine8790
S

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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