please answer steps 2-13 CORRECTLY for a like please and thank you!

Home Insert fr v V TA v le f ? AutoSum Recently Financial Logical Used Text Insert Function Date & Lookup & Math & Time Reference Trig Fur B21 x fx =B18A20 B D E F Ticket Order Form 3 Invoice Information 4 Invoice Date 5 Invoice Time 6 Taken By 7 Invoice Number 8 9 Customer Information 10 Customer Name 11 Address 12 City 13 State 14 Zip 15 Phone 16 17 Ticket Information 18 Number of tickets 19 Ticket Location 20 Cost of Ticket 21 Total Invoice Amou 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 HVALUEI AutoSave OFF BG Toss Home Insert Draw Page Layout Formulas Data Review View Tell me fx V V Av v le - Define Nar 2 AutoSum Recently Financial Logical Used Text Insert Function Date & Lookup & Math & Time Reference Trig More Functions Createfror A1 fx Ticket Location D E F G H 1 1 Ticket Location 2 Balcony Back $ 15.00 3 Floor Back $ 20.00 4 Balcony Front $ 25.00 5 Floor Middle $ 30.00 6 Floor Front $ 35.00 7 8 Back to TicketOrder 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 25 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 WEWNECK TicketOrder TicketData Employees Macro + Ready AutoSave OFF Due Home Insert Draw Page Layout Formulas Data Review Vi fx V 2 V A v Q le Text Insert Function AutoSum Recently Financial Logical Used Date & Lookup & Math & Time Reference Trig ME Func J23 Xfx B D E F G H 1 Employees 2 JUNI 452 3 Leslie 652 4 Mark 854 5 Ned 659 6 Phillip 554 7 8 9 10 11 12 13 14 s 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 NN A1 X fx A B c D M N 1 2 4 5 9 7 8 9 10 12 13 14 15 16 17 18 19 20 21 22 22 24 25 26 27 BE 29 30 31 32 33 DE 35 36 37 38 39 40 41 42 43 44 TicketOrder TicketData Employees Macro + Ready DO Steps to Perform: Step 1 Enable content il necessary. 2 Instructions Grader will automatically add you last name to the start of the filename. Save the fletore location where you are storing your files as a macro-enabled workbook. Start Excel Open the downloaded Excel file named Excel CHO8_Assessment_Tickets Correct the formula in cell B21 by entering the correct cells (the formula should multiply the On the TicketOrder worksheet, use Trace Precedents to illustrate the formula error in cel B21 Number of tickets by the Cost of Ticket). Using the formula created in cell B21. add an IFERROR formula to display a blank cell there be used. Enter the Input Message Title Date Enter the Message Enter today's data type the Add data validation to cell B4 that allows only the current date. The TODAY) fundion should period). Enter the Error Alert Title Error Enter the Error Message Date must be today's date (type the period). In cell B4, enter =TODAYO. 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, Enter the Error Message Time must be between 9:00 AM and 4:00 PM In cell B5, type 2:00 PM 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. In cell B6, select Ned. are no results 3 4 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. For the logical test is testing if cell B4 is greater than 0. If the result of the test is true, then TEXT(B4. "YYYYMMDD"), **)& "&IF(B5>0,TEXT(B5. "HHMM"), ***)& * &IF(86>0, VLOOKUP(B6, Employees!A2:86.2) If the result of the test is false, then return a blank. 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. 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. In cell B19. select Balcony Front 7 Step Instructions Point Possil 9 2 Insert a hyperlink in cell A17 on the TicketOrder worksheet that links to cell A8 of the TicketData worksheet. Add the Screen Tip 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 Screen Tip Go to Ticket Order 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.019. 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. 10 After creating the macro, click the Undo button to undo the change you made. 11 Add a form control button and assign the ClearData macro to the button. Change the button captions to Clear Data. Test the button. 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). On the TicketOrder worksheet, unlock cells A17, B4:B6, B10:815, and B18:819. From the Format Cells dialog box, hide all cells that have formulas: B7, B20, and B21. 12 13 14 Hide the horizontal and vertical scroll bars. Note, Mac users can skip hiding the scroll bars. Hide the row and column headers. 15 16 Hide the gridlines Protect the worksheet. Save the workbook as a macro-free workbook, Mark the workbook as final. Close the Excel CHO8 Assessment_Ticket.xlsx workbook. Exit Excel. Submit your file as directed 17 Total Points Home Insert fr v V TA v le f ? AutoSum Recently Financial Logical Used Text Insert Function Date & Lookup & Math & Time Reference Trig Fur B21 x fx =B18A20 B D E F Ticket Order Form 3 Invoice Information 4 Invoice Date 5 Invoice Time 6 Taken By 7 Invoice Number 8 9 Customer Information 10 Customer Name 11 Address 12 City 13 State 14 Zip 15 Phone 16 17 Ticket Information 18 Number of tickets 19 Ticket Location 20 Cost of Ticket 21 Total Invoice Amou 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 HVALUEI AutoSave OFF BG Toss Home Insert Draw Page Layout Formulas Data Review View Tell me fx V V Av v le - Define Nar 2 AutoSum Recently Financial Logical Used Text Insert Function Date & Lookup & Math & Time Reference Trig More Functions Createfror A1 fx Ticket Location D E F G H 1 1 Ticket Location 2 Balcony Back $ 15.00 3 Floor Back $ 20.00 4 Balcony Front $ 25.00 5 Floor Middle $ 30.00 6 Floor Front $ 35.00 7 8 Back to TicketOrder 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 25 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 WEWNECK TicketOrder TicketData Employees Macro + Ready AutoSave OFF Due Home Insert Draw Page Layout Formulas Data Review Vi fx V 2 V A v Q le Text Insert Function AutoSum Recently Financial Logical Used Date & Lookup & Math & Time Reference Trig ME Func J23 Xfx B D E F G H 1 Employees 2 JUNI 452 3 Leslie 652 4 Mark 854 5 Ned 659 6 Phillip 554 7 8 9 10 11 12 13 14 s 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 NN A1 X fx A B c D M N 1 2 4 5 9 7 8 9 10 12 13 14 15 16 17 18 19 20 21 22 22 24 25 26 27 BE 29 30 31 32 33 DE 35 36 37 38 39 40 41 42 43 44 TicketOrder TicketData Employees Macro + Ready DO Steps to Perform: Step 1 Enable content il necessary. 2 Instructions Grader will automatically add you last name to the start of the filename. Save the fletore location where you are storing your files as a macro-enabled workbook. Start Excel Open the downloaded Excel file named Excel CHO8_Assessment_Tickets Correct the formula in cell B21 by entering the correct cells (the formula should multiply the On the TicketOrder worksheet, use Trace Precedents to illustrate the formula error in cel B21 Number of tickets by the Cost of Ticket). Using the formula created in cell B21. add an IFERROR formula to display a blank cell there be used. Enter the Input Message Title Date Enter the Message Enter today's data type the Add data validation to cell B4 that allows only the current date. The TODAY) fundion should period). Enter the Error Alert Title Error Enter the Error Message Date must be today's date (type the period). In cell B4, enter =TODAYO. 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, Enter the Error Message Time must be between 9:00 AM and 4:00 PM In cell B5, type 2:00 PM 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. In cell B6, select Ned. are no results 3 4 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. For the logical test is testing if cell B4 is greater than 0. If the result of the test is true, then TEXT(B4. "YYYYMMDD"), **)& "&IF(B5>0,TEXT(B5. "HHMM"), ***)& * &IF(86>0, VLOOKUP(B6, Employees!A2:86.2) If the result of the test is false, then return a blank. 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. 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. In cell B19. select Balcony Front 7 Step Instructions Point Possil 9 2 Insert a hyperlink in cell A17 on the TicketOrder worksheet that links to cell A8 of the TicketData worksheet. Add the Screen Tip 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 Screen Tip Go to Ticket Order 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.019. 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. 10 After creating the macro, click the Undo button to undo the change you made. 11 Add a form control button and assign the ClearData macro to the button. Change the button captions to Clear Data. Test the button. 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). On the TicketOrder worksheet, unlock cells A17, B4:B6, B10:815, and B18:819. From the Format Cells dialog box, hide all cells that have formulas: B7, B20, and B21. 12 13 14 Hide the horizontal and vertical scroll bars. Note, Mac users can skip hiding the scroll bars. Hide the row and column headers. 15 16 Hide the gridlines Protect the worksheet. Save the workbook as a macro-free workbook, Mark the workbook as final. Close the Excel CHO8 Assessment_Ticket.xlsx workbook. Exit Excel. Submit your file as directed 17