# Automatic data entry in cell B, after data entry in cell A / Employees Break List Reservation

#### Olisthoughts

##### New Member
Hi,

I'm making a break list for my team at my new job.

No more than 3 employees are allowed to go on break at the same time, and I want a 4th employee (and more) to be able to reserve a break as soon as the 3rd employee comes back.

There are 3 types of breaks allowed "30 min Lunch break, two 15 Min breaks, and one 7 minute break". In the reservations column, when selecting a cell the 4th employee should have as a drop-down menu to select the type of break. (essentially selecting either a lunch break, a 15 min break, or a 7 min break) When a 3rd employee comes back - the current time is input in the cell under the "start time" column for the break selected under reserved.

Since more employees should be able to reserve their breaks - the current time should be associated with the reservation. When a 3rd employee is back - the reservation with the smallest value would be activated.

I'm assuming the pseudo-code would be something like "If currently on break is equal or smaller than 2, then calculate smallest value in the reservations column AND match text (type of break), and enter current time in the same row as smallest value, under start time column with matching break text". Since I'm new to Excel I don't know how to write the actual code/formula, or if it's even possible for anyone to do.

Another fantasy that I'm pretty sure is impossible was for as soon as current time is input as a result of the reservation, to have a notification pop-up on screen, or send an e-mail to the person who's reserved break has started, since they don't know when someone actually came back.

Each break has 4 columns (Start time = double-click for time input), Estimated return time = adds the time allowed for the break to the current time, calculating est return time). Actual return time = current time input, and Actual break time = Subtracts start time from Actual return time.

I'm using a Helper column to accurately calculate employees on break (without it 3 open breaks from the same employee were counted as 3 employees).

Also important for context: The break list is likely to be uploaded and used from Microsoft Teams, where as I understand macros and VBA don't work. But formulas still do.

The codes that I use in local Excel:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("c:u")) Is Nothing Then
Cancel = True
Target.Formula = Time
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim cell As Range

' See if updated cells in watched range
Set rng = Intersect(Target, Range("c:u"))

' Loop through cells in watched range
If Not rng Is Nothing Then
For Each cell In rng
If cell <> "" Then
cell.Locked = True
End If
Next cell
End If
End Sub

#### Attachments

• Screenshot_59.png
61.4 KB · Views: 2

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Replies
3
Views
222
Replies
34
Views
2K
Replies
13
Views
189
Replies
1
Views
82
Replies
10
Views
373

### Forum statistics

1,127,589
Messages
5,625,671
Members
416,125
Latest member
NeedExcelHelp2021

### 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.

### Which adblocker are you using?

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

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