Office 2016 – myitlab:grader – Instructions Excel Project YO_Excel16_Ch08_PS1_v2 Project Description: You have just started working in the ticket office for a local theater. The theater has five different employees who take ticket orders over the phone. Until now, they have been taking the orders on paper and then retyping the information on an invoice. Jill, one of the employees, started an Excel workbook to enter the ticket orders in, but the other employees kept making mistakes and deleting the formulas, so they ended up retyping the invoices anyway. You will help Jill modify her workbook so data validation makes data entry easier, macros help clear all the data except the formulas, and various types of workbook protection prevent formulas from being deleted by mistake. Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Start Excel. Open the downloaded Excel file named e04ch08_grader_hw_Ticket.xlsx. Save the file as a Macro Enabled Workbook named e04ch08_grader_hw_Ticket_LastFirst, using your last and first name. Enable content if necessary. 0 2 3 On the TicketOrder worksheet, use Trace Precedents to illustrate the formula error in cell B21. Correct the formula in cell B21 by entering the correct cells (the formula should multiply the Number of tickets by the Cost of Ticket). Using the formula created in cell B21, add an IFERROR formula to display a blank cell if there are no results. Add data validation to cell B4 that allows only the current date. The =TODAY() function should be used. Enter the Input Message Title Date Enter the Message Enter today’s date. (type the period). Enter the Error Alert Title Error Enter the Error Message Date must be today’s date. (type the period). In cell B4, enter =TODAY(). 5 10 4 Add data validation to cell B5 so that only a time between 9:00 AM and 4:00 PM can be entered. Enter the Input Message Enter the time in the HH:MM AM/PM format. (type the period). Enter the Error Message Time must be between 9:00 AM and 4:00 PM. (type the period). In cell B5, type 2:00 PM. 10 5 Add data validation to cell B6 to look up a list of names from the Employees worksheet. Enter the Input Message Choose an employee name from the list. (type the period). In cell B6, select Ned. 10 6 Add a formula to cell B7 that creates an Invoice Number from the date, the time, and the ID number on the Employees worksheet for the employee listed in cell B6. The formula will include an IF function, the TEXT function, and a VLOOKUP function. (Review the example created in the Prepare Case, on the Invoice worksheet, in cell E4.) 5 7 Add data validation to cell B18 so only a whole number between 1 and 25 can be entered in the cell. Enter the Input Message Enter a number between 1 and 25. (type the period). In cell B18, type 4. 10 8 Add data validation to cell B19 to look up a list of ticket locations from the TicketData worksheet. Enter the Input Message Select a location from the list. (type the period). In cell B19, select Balcony Front. 11 9 Insert a hyperlink in cell A17 on the TicketOrder worksheet that links to cell A8 of the TicketData worksheet. Add the ScreenTip Go to Ticket Data Insert a hyperlink in cell A8 on the TicketData worksheet that links to cell A17 of the TicketOrder worksheet. Add the ScreenTip Go to Ticket Order 10 Updated: 05/23/2018 1 Current_Instruction.docx Office 2016 – myitlab:grader – Instructions Step 10 Excel Project Points Possible Instructions If necessary, add the Developer tab to the ribbon. Create an absolute macro on the TicketOrder worksheet that will clear the contents of all cells containing data entered by the user. This includes the cell ranges B4:B6, B10:B15, and B18:B19. Select all three ranges before clearing the contents. Make cell B4 the active cell after the macro is run. Name the macro ClearData and assign the letter d as the shortcut key. 0 After creating the macro, click the Undo button to undo the change you made. 11 On the Developer tab, in the Code group, click Macros, and then click Edit. Copy the macro code and close the Microsoft Visual Basic for Applications window. Click the Macro worksheet and paste the copied macro code in cell A1 (there should be 11 lines altogether). 6 12 On the TicketOrder worksheet, unlock cells A17, B4:B6, B10:B15, and B18:B19. 8 13 From the Format Cells dialog box, hide all cells that have formulas: B7, B20, and B21. 5 Hide the horizontal and vertical scroll bars. Note, Mac users can skip hiding the scroll bars. 14 4 Hide the row and column headers. 15 Hide the gridlines. Protect the worksheet. Protect the workbook structure. 6 16 Save the workbook as a macro-free workbook. Click Yes. Mark the workbook as final. Close the workbook, exit Excel, and then submit your file as directed by your instructor. 0 Total Points Updated: 05/23/2018 2 100 Current_Instruction.docx Ticket Order Form Invoice Information Invoice Date Invoice Time Taken By Invoice Number Customer Information Customer Name Address City State Zip Phone Ticket Information Number of tickets Ticket Location Cost of Ticket Total Invoice Amount #VALUE! 20180615154345e04ch08_grader_hw_ticket_fernandezcynthia.xlsx Ticket Location Balcony Back $ 10,00 Floor Back $ 15,00 Balcony Front $ 20,00 Floor Middle $ 25,00 Floor Front $ 30,00 Back to TicketOrder 20180615154345e04ch08_grader_hw_ticket_fernandezcynthia.xlsx Employees Jill Leslie Mark Ned Phillip 452 652 854 659 554 20180615154345e04ch08_grader_hw_ticket_fernandezcynthia.xlsx
Purchase answer to see full attachment