The staff at Painted Paradise Golf Resort and Spa are required to wear uniforms every day. While the hotel provides uniforms each year to staff members, occasionally they want to order items in addition to what is provided to them.
accounting
Description
YO16_XL_BU04_GRADER_PS1_HW - Uniforms 1.5
Project Description:
The staff at Painted Paradise Golf Resort and Spa are required to wear
uniforms every day. While the hotel provides uniforms each year to staff
members, occasionally they want to order items in addition to what is provided
to them. One of the recent interns at the hotel created a spreadsheet order
form, but never finished it. You will finish working on it so it can be distributed
to the staff. You will add data validation rules, a macro, and make it look
less like an Excel spreadsheet and more like an online order form so it is easy
to use.
Steps to
Perform:
|
Step |
Instructions |
Points Possible |
|
1 |
Start Excel. Open the downloaded Excel file named e04_grader_h1_Uniforms.xlsx. Click OK
in the circular reference warning box. Save the workbook as a Macro-Enabled
workbook with the file name e04_grader_h1_Uniforms_LastFirst, using your
last and first name. Enable content, if necessary. |
0 |
|
2 |
On the OrderForm worksheet,
locate and correct the circular reference. |
5 |
|
3 |
Select cell C8. Enter a calculated value for the Order
Number that uses the employee name followed by the order date in the MMDDYY format.
The order number should only be created if C6 is not blank, and there should
be a space between employee name and order number. |
15 |
|
4 |
Select cell C10 and enter a data
validation rule to look up the department name from the cell range named Departments named
range on the Options worksheet. The input message should read Select
a
department from the list. The error message should read Invalid
department. Include the period in both messages. Select cell C10
and select Front Desk. |
10 |
|
5 |
Select cells C13:C18, and enter a data validation rule to
look up the top options from the cell range named TopOptions on the Options
worksheet. Leave the input message and error message blank. Select cell C13
and select Vest. |
5 |
|
6 |
Select cells C19:C24, and enter
a data validation rule to look up the bottom options from the cell range
named BottomOptions
on the Options worksheet. Leave the input message and error
message blank. |
5 |
|
7 |
Select cells C25:C30, and enter a data validation rule to
look up the other options from the cell range named OtherOptions on the Options
worksheet. Leave the input message and error message blank. |
5 |
|
8 |
Select cells D13:D30, and enter
a data validation rule to lookup the size options from the cell range named Sizes
on the Options worksheet. Leave the input message and error message blank. |
5 |
|
9 |
Select cell A13 and insert a hyperlink to the defined name
Tops. Add a ScreenTip Go to Top
Options |
10 |
|
10 |
If necessary, add the Developer
tab to the Ribbon. Record a macro named ClearForm with the shortcut key F and the
description
To clear the form for another order. (including the period). Clear
the contents from cells C6:C7, C10, C13:E30 and C33. Click cell C6 and stop
recording the macro. Undo the changes you made while recording the macro. |
25 |
|
11 |
Modify the workbook for easier navigation: Hide the
gridlines on the OrderForm worksheet. Hide the horizontal scroll bar. Hide
row and column headings. Unlock the cells required for data entry (the same
cells the macro clears). Protect the OrderForm worksheet (do not use a
password). |
15 |
|
12 |
Save the workbook and click Yes
to save it as a macro-free workbook. Close the workbook, close Excel, and
submit e04_grader_h1_Uniforms_LastFirst.xlsx
with the following worksheets: OrderForm, Options, and Macro to your
instructor as directed. |
0 |
|
Total Points |
100 |
