YO19_Excel_BU04_Assessment1_Hotel_Schedule_Instructions
.docx
keyboard_arrow_up
School
Stark State College *
*We aren’t endorsed by this school
Course
105
Subject
Information Systems
Date
Jan 9, 2024
Type
docx
Pages
2
Uploaded by CorporalIronSalmon37
Grader - Instructions
Excel 2019 Project
YO19_Excel_BU04_Assessment1_Hotel_Schedule
Project Description:
The hotel at the Painted Paradise Golf Resort and Spa needs a better way to track employee hours. Ideally, each department will use an Excel spreadsheet to track the hours, and that way data is easily accessible and can be shared. A former employee
created a template to use, but it still needs work before it can be handed over to the managers to use.
Steps to Perform:
Step
Instructions
Points Possible
1
Start Excel. Downloaded and open the file named Excel_BU04_Assessment_HotelSchedule.xlsx
. Grader has automatically added your last name to the beginning of the file name. Save the file to a location where you are storing your files.
0
2
On the Schedule worksheet, create a list validation rule in cells A7:A26 with the source data from cells A3:A12 on the Employees worksheet. Enter the Input message Choose a name from the list.
Enter the message Invalid name.
(include the periods). In cell A7, select Eric Mosley
.
12
3
In cells C7:I26, create a list validation rule with the source data from cells A3:A9 on the Shifts worksheet. Leave the Input and Error messages blank.
10
4
In cell C3, create an any value validation rule with an Input message that says Enter the starting date for the week.
(include the period). Type =TODAY()
in cell C3.
14
5
Select cell J27, and then correct the formula so there is no longer a circular reference.
8
6
On the Employees worksheet, delete column B. This will cause a #REF! error on the Schedule
worksheet. Download the Excel_BU04_HotelEmployee.xlsx
workbook and save it in the same folder as your HotelSchedule workbook. Open the downloaded Excel_BU04_HotelEmployee.xlsx
workbook. On the HotelSchedule workbook, click the Schedule worksheet and modify the table array reference in the VLOOKUP formulas in cells B7:B26 to reference cells A6:B15 (named Employee) on the HotelEmployee workbook. Close the Excel_BU04_HotelEmployee.xlsx workbook.
14
7
On the Schedule worksheet, select cell A6, and then insert a hyperlink to go to the Employees worksheet. Add a ScreenTip, Go to the Employee worksheet (no period). On the Employees
worksheet, select cell D2, and then insert a hyperlink to go to the Schedule worksheet. Add a ScreenTip, Go to the Schedule worksheet (no period).
10
8
If necessary, add the Developer tab to the ribbon. On the Schedule worksheet with cell A6 selected, create a new macro named NewWeek
with a keyboard shortcut n
and a description Clears cells for the new week.
(include the period). Select cells C3, B29, A7:A26 and C7:I26 (in that order), and then click Clear Contents. Click C3 and then stop recording the macro. Undo the changes the macro made.
Display the macro code in the Visual Basic Editor. Copy the code, beginning with Sub NewWeek() and ending with End Sub
. Paste the copied code on the Macro worksheet in cell A1.
10
9
On the Schedule worksheet, unlock the cells required for data entry (C3, A7:A26, C7:I26 and B29). Hide the gridlines.
6
10
On the Schedule worksheet, hide columns K:R. Hide all formulas in cells B7:B26, J7:J27 and 16
Created On: 03/21/2022
1
YO19_Excel_BU04_Assessment1 - Hotel Schedule 1.2
Grader - Instructions
Excel 2019 Project
Step
Instructions
Points Possible
C27:I28. Hide row and column headers. Protect the Schedule worksheet (do not use a password). 11
Save the workbook as a macro-free workbook with the following worksheets: Schedule, Shifts,
Employees, and Macro. Close the file, close Excel, and submit the file e04HotelSchedule_LastFirst.xlsx as directed.
0
Total Points
100
Created On: 03/21/2022
2
YO19_Excel_BU04_Assessment1 - Hotel Schedule 1.2
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help