You want to develop, using Excel, a log for security guards to track entries into the secured
Question:
You want to develop, using Excel, a log for security guards to track entries into the secured area of a laboratory and record any incidences. We expect there to be 2-20 entries each weekday and 1-4 entries each weekend for the rest of the year. You will need the following information recorded: date, time, name of the opening the door, the authorized person entering the lab (with the employee badge number automatically filling in, these are listed on the next page for you to copy for looking up), the number of test tubes brought in, and space for noting any incidences. This information will have data validation with the date restricted to only between 4/1/23 and 12/31/23 and a time only between 7am and 7pm. If the date or time is entered outside the allowed boundaries, have an appropriate error alert with good guidance for the user of the log. If you are able to do so, force it so that dates and times in the future may not be entered (this is a little trickier, so dont stress if you cannot do this part). The employee names will be chosen from a drop-down menu referencing the appropriate listings below. The number of test tubes must be a whole number between 0 and 10. Near the top of the log, have a running total of the number of test tubes taken into the laboratory (assume it starts at 0). This should be a formula reaching into the appropriate cells and should be protected so no one accidently overwrites the formula. There should also be cells near the top showing the current date and time. There is no need for any data validation for the incidents. Freeze the column labels so they remain visible, even if one scrolls about.
Authorized access listing (updated 3/1/23) | |||||||||||
employee | badge | ||||||||||
Jessica | 1001 | ||||||||||
Mateo | 1005 | ||||||||||
Ashley | 1009 | ||||||||||
Matthew | 1010 | ||||||||||
Joshua | 1011 | ||||||||||
David | 1017 | ||||||||||
Yosef | 1020 | ||||||||||
Sarah | 1022 | ||||||||||
Jacob | 1023 | ||||||||||
Arnav | 1040 | ||||||||||
John D. | 1062 | ||||||||||
Pari | 1065 | ||||||||||
John K. | 1089 | ||||||||||
Sara | 1102 | ||||||||||
Francisco | 1118 | ||||||||||
Antonia | 1122 | ||||||||||
Benjamin | 1127 | ||||||||||
| |||||||||||
|
|
Example: