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

Olisthoughts

New Member
Joined
Apr 16, 2020
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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.

I'll attach a screenshot and more info for context.

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
ActiveSheet.Unprotect Password:="Break"
cell.Locked = True
ActiveSheet.Protect Password:="Break"
End If
Next cell
End If
End Sub
 

Attachments

  • Screenshot_59.png
    Screenshot_59.png
    61.4 KB · Views: 2

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Watch MrExcel Video

Forum statistics

Threads
1,127,105
Messages
5,622,758
Members
415,926
Latest member
jerrynababa

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
Top