Follow the instructions below. Show formulas and results separately on excel spreadsheet
Grader - Instructions Excel 2016 Project Step Instructions Points Possible 17 0.5 On the Invoice worksheet, add a Button (Form Control) in the top left corner of cell G2, and then drag to the bottom right corner of cell H3 to draw the button. Assign the ClearSeus macro to the button. Edit the button text to Clear Invoice. Test the button. Create an absolute reference macro named ClearFormatting with the shortcut key k and the description to clear special highlighting from the invoice. The macro will select the cell range B15:F30, remove the bold font, change the font size back to 9, and then click cell B15. 18 0.4 Launch Visual Basic for Applications (VBA) to view the actual code for the macros you recorded. All macros you have recorded will show in the window, separated from one another by a horizontal line. Scroll if necessary to see the VBA code for the ClearFormatting macro. Copy the text that starts with Range ("B15:F30") and ends with Range("B15:C15").Select Scroll to the top of the VBA window to see the ClearCells macro. Place your insertion point after the line Range ("E6"). Select Select Paste. Close and Return to Microsoft Excel. Click cell B15 and select Facial - Mud & Citrus. Run the Highlightitem macro. Click the Clear Invoice button. If necessary, click the Home tab, and verify that the formatting in row 15 was changed back to size 9, not bold. 19 0.3 On the Invoice worksheet, if necessary, click the Developer tab. In the Code group, click Macros. In the Macro dialog box, in the Macro name list, select ClearCells, and then click Edit. A new Visual Basic for Applications (VBA) window opens with the actual code for the macros you recorded. All macros you have recorded will show in the window, separated from one another by a horizontal line. Copy all the code from the ClearCells macro, beginning with Sub ClearCells and ending with End Sub. Click the Macro worksheet tab, and then paste that code beginning in cell A1. Return to the VBA window and copy all of the code from the Highlightlteno, macro, beginning with Sub Highlightitem) and ending with End Sub. Click the Macro worksheet tab and paste that code beginning in cell H1. Return to the VBA window and copy all of the code from the Clear Formatting macro, beginning with Sub ClearFormatting and ending with End Sub. Click the Macro worksheet tab and paste that code beginning in cell M1. Save the workbook as an Excel Workbook (.xlsx). 20 On the Invoice worksheet, in cell D14, insert a hyperlink to cell F2 on the Prices worksheet. Add the screentin. Go to Prices worksheet On the Prices worksheet, in cell F2, insert a hyperlink to cell D14 on the Invoice worksheet. Add the Screen Tip Return to Invoice worksheet Hide all worksheets except Invoice. On the Invoice worksheet, unlock cells E6, E8, E10, E12, D14, C31, F34, and cell ranges B15:B30 and E15:E30. Protect the Invoice worksheet and do not allow for locked cells to be selected. Press HOME On the Invoice worksheet, remove the Gridlines. 0.4 Unprotect the Invoice worksheet. On the Invoice worksheet, hide all formulas, and then protect the sheet again. 0.5 On the Invoice worksheet, hide the horizontal scroll bar. 0.3 Protect the workbook structure. Do not add a password. 0.5 Grader. Poruciana Excel 2016 Project Crador - Insouciana Excel 2018 Project YO16_XL_CH08_GRADER_PC_HW - Spa 1.4 Step Instructions Points Possible 04 Project Description: Mada Fodate, manager of the Turquoise Oasis Spa, wants to improve the layout of the existing apa invoice and automate the invoice process as much as possible to ensure data accuracy and consistency. The invoice currently has formulas in the Charge/Hour and Amount columns, but they often get deleted by mistake. The Therapist rame is often misspelled, the room rumber is often wrong and Moda thinks the suhtotal amount may not be calculating correctly. Another problem anses when the description of the service is not entered correctly and then the charge hour cannot be found in the lookup table On the Invoice workshee, incall E rnar a Time data valdation that will limit entry to start time of 8:00 AM and end me 4:30 PM Enter the input title, Appointment time Enter the input massage Enter the appointment time as HH:MM AMPM. Enter the Error Alert Title Error Enter the Error message The time must be between 8:00 AM and 4:30 PM Sclect cel EB type 2:30 PM Steps to Perform: Step Instructions Paints Possible On the Invoice worksheet, incall E12, enter a whole number Data Valdarion that will alon numbers between 1001 and 5140 Select cel E 12. type 1001 Open the downloaded file edah08 grader_pc_Sos.xix. Save the file as 04ch08_grader pc Spa LastFirst, using your last and frst name. If a Security Warning message displays, alick tha Enable Editing button. On the invoice workshoot, in collC31, anter a text length Data Valdation that iimts the text length to less than or equal to 180 Enter the input message de Comments Enter the input message Comments may not be more than 180 characters. Enter the error Alert Title Error Enter the Error Alert Message Must be 180 characters or less On the Invoice worksheet, vetrece precedents and trace dependents to look at the formulas to make sure they are constructed properly. Fix any errors you find. 0.4 On the invoice worksheet use Evaluate Fomula to determine what is wrong with the formula in cel F93. Fix the formule. On the invoice worksheet, in cell D33, anter a data validation that will display data entry prompts. Enter the input message Tax rate Enter the input message All items and services require sales tax. On the Invoice worksheet, identify and correct the circular reference. Note, Mac users can skip this step. On the Invoice worksheet, add the Watch Window. Add walch for FS1, F33, and F95. Move the Watch Window to the top of the worksheet window. Select orl E15, typa 2. Notice that all the cells in the Watch Window changed Close the watch Window On the Invice worksheet, in cell E12, enviar a custom data validation Select the existing text, And then replace it with AND LEFT|E12,1 TEXTE6. "YYYYMMDD","&" "&IFEBO,TEXTIES, HHMM"),"&"FE100, VLOOKUP(E10 Therapists 2). 0.5 0.5 Enter a Deo mal data validation for all range E10-E30 to allow for decimals less than ar equal to 2 Enter the input message the Hours Enter the input message Enter the number of service hours. Enter a Warning alert. Enter the ste Invalid value Enter the Error massage The hours you entered exceed the maximum recommended. If necessary, add the Developer tab. Create a macro named ClearCalls with a shortcut key or C and the description To clear contents from cells The macra will clear the contents from cells ES, ES, E10, E12, and the cell ranges 31:32, 815 630, and E15:30. The macro will make cel E6 The active call after it is ran Save the workbook 8B 8 macro-enabled workbook with the filename e04ch08 grader pe Spa LastFirst In call Es, enter today's date. In oal EB anter2:30 PM. in coll F10, selectistas Christy, Test the Macro 0.4 On the Invoice worksheet, in cell El enter a Date validation that verifies the date is less than or us to TODAYO Enter the input it involo Dato Enter the input massage Enter the data in the following format: MM/DD/YYYY. Enter the Error alert title Error Enter the Error message box Future dates are not allowed Select cel E6, type TODAY! On the Invoice worksheet, in cell B16, choose Facial - Mud & Citrus. In cel E16, type 1. Sclect cel e 16 Create a Relative References Macro named Highlighiltace with the shortcut keyh and the description In highlight an invoice special. The macro will select cells B15:F15 and then change the font to Bold, size 14. Click call B16, and then select Hair Coloring. Test the macra. Created On: 07/06/2019 YO16_XL_CHOB_GRADER_PC_HW-Spa 14 Crased On 07/05/2019 YO16_XL_CHOB_GRADER_PC_HW - Se 14 INVOICE Turquoise Oasis Spa A Passion for Helping People Relax INVOICE #: DATE: TIME: THERAPIST: 3356 Hemmingway Circle Santa Fe, NM 87594 Phone: 505.555. SPA1 Fax: 505.555. SPAX ROOM: DESCRIPTION CHARGE/HOUR HOURS AMOUNT Mud Bath $ 100.00 150.00 1.00 $ 1.00 $ 100.00 150.00 Reflexology COMMENTS: SUBTOTAL $ 150.00 TAX RATE 6.50% #VALUE! Make all checks payable to Turquoise Oasis Spa. THANK YOU FOR YOUR BUSINESS! SALES TAX OTHER TOTAL #VALUE! Invoice Prices Products Therapists Macro + Return to invoice Product Facial - Mud & Citrus Facial - Steam Exfoliation Hair Coloring Hair Style & Cut - Men Hair Style & Cut - Women Makeup Consultation Manicure & Pedicure Package Manicure & Polish Pedicure & Polish Waxing - Body Waxing - Facial Aroma & Sound Therapy Package Aroma Therapy Body Detox Electrotherapy Body Detox Steam Therapy Massage - Deep Tissue Massage - Deep Tissue, Couples Massage - Fusion Massage - Fusion, Couples Massage - Shiatsu Mud Bath Reflexology Sound Therapy Steam Bath Tibetan Reiki Therapy Location Salon Salon Salon Salon Salon Salon Salon Salon Salon Salon Salon Spa Spa Spa Spa Spa Spa Spa Spa Spa Spa Spa Spa Spa Spa C D Price Unit 100.00 Each 75.00 Each 100.00 Each 35.00 Each 50.00 Each 1100.00 Each 70.00 Each 45.00 Each 45.00 Each 75.00 Each 25.00 Each 100.00 Hour 75.00 Hour 150.00 Hour 100.00 Hour 125.00 Hour 112.50 Hour 150.00 Hour 137.50 Hour 150.00 Hour 100.00 Hour 150.00 Hour 75.00 Hour 65.00 Hour 225.00 Hour Invoice Prices Products Therapists Macro B C D E F PRODUCTS Aroma & Sound Therapy Package Aroma Therapy Body Detox Electrotherapy Body Detox Steam Therapy Massage - Deep Tissue Massage - Deep Tissue, Couples Massage - Fusion Massage - Fusion, Couples Massage - Shiatsu Mud Bath Reflexology Sound Therapy Steam Bath Tibetan Reiki Therapy Invoice Prices Products Therapists Macro C D E THERAPISTS Istas, Christy Mault, Kendra Niese, Jason EMPLOYEE_NUMBER 3054 1477 5901 Invoice Prices Products Therapists A B C D E F G H Invoice Prices Products Therapists Macro Grader - Instructions Excel 2016 Project Step Instructions Points Possible 17 0.5 On the Invoice worksheet, add a Button (Form Control) in the top left corner of cell G2, and then drag to the bottom right corner of cell H3 to draw the button. Assign the ClearSeus macro to the button. Edit the button text to Clear Invoice. Test the button. Create an absolute reference macro named ClearFormatting with the shortcut key k and the description to clear special highlighting from the invoice. The macro will select the cell range B15:F30, remove the bold font, change the font size back to 9, and then click cell B15. 18 0.4 Launch Visual Basic for Applications (VBA) to view the actual code for the macros you recorded. All macros you have recorded will show in the window, separated from one another by a horizontal line. Scroll if necessary to see the VBA code for the ClearFormatting macro. Copy the text that starts with Range ("B15:F30") and ends with Range("B15:C15").Select Scroll to the top of the VBA window to see the ClearCells macro. Place your insertion point after the line Range ("E6"). Select Select Paste. Close and Return to Microsoft Excel. Click cell B15 and select Facial - Mud & Citrus. Run the Highlightitem macro. Click the Clear Invoice button. If necessary, click the Home tab, and verify that the formatting in row 15 was changed back to size 9, not bold. 19 0.3 On the Invoice worksheet, if necessary, click the Developer tab. In the Code group, click Macros. In the Macro dialog box, in the Macro name list, select ClearCells, and then click Edit. A new Visual Basic for Applications (VBA) window opens with the actual code for the macros you recorded. All macros you have recorded will show in the window, separated from one another by a horizontal line. Copy all the code from the ClearCells macro, beginning with Sub ClearCells and ending with End Sub. Click the Macro worksheet tab, and then paste that code beginning in cell A1. Return to the VBA window and copy all of the code from the Highlightlteno, macro, beginning with Sub Highlightitem) and ending with End Sub. Click the Macro worksheet tab and paste that code beginning in cell H1. Return to the VBA window and copy all of the code from the Clear Formatting macro, beginning with Sub ClearFormatting and ending with End Sub. Click the Macro worksheet tab and paste that code beginning in cell M1. Save the workbook as an Excel Workbook (.xlsx). 20 On the Invoice worksheet, in cell D14, insert a hyperlink to cell F2 on the Prices worksheet. Add the screentin. Go to Prices worksheet On the Prices worksheet, in cell F2, insert a hyperlink to cell D14 on the Invoice worksheet. Add the Screen Tip Return to Invoice worksheet Hide all worksheets except Invoice. On the Invoice worksheet, unlock cells E6, E8, E10, E12, D14, C31, F34, and cell ranges B15:B30 and E15:E30. Protect the Invoice worksheet and do not allow for locked cells to be selected. Press HOME On the Invoice worksheet, remove the Gridlines. 0.4 Unprotect the Invoice worksheet. On the Invoice worksheet, hide all formulas, and then protect the sheet again. 0.5 On the Invoice worksheet, hide the horizontal scroll bar. 0.3 Protect the workbook structure. Do not add a password. 0.5 Grader. Poruciana Excel 2016 Project Crador - Insouciana Excel 2018 Project YO16_XL_CH08_GRADER_PC_HW - Spa 1.4 Step Instructions Points Possible 04 Project Description: Mada Fodate, manager of the Turquoise Oasis Spa, wants to improve the layout of the existing apa invoice and automate the invoice process as much as possible to ensure data accuracy and consistency. The invoice currently has formulas in the Charge/Hour and Amount columns, but they often get deleted by mistake. The Therapist rame is often misspelled, the room rumber is often wrong and Moda thinks the suhtotal amount may not be calculating correctly. Another problem anses when the description of the service is not entered correctly and then the charge hour cannot be found in the lookup table On the Invoice workshee, incall E rnar a Time data valdation that will limit entry to start time of 8:00 AM and end me 4:30 PM Enter the input title, Appointment time Enter the input massage Enter the appointment time as HH:MM AMPM. Enter the Error Alert Title Error Enter the Error message The time must be between 8:00 AM and 4:30 PM Sclect cel EB type 2:30 PM Steps to Perform: Step Instructions Paints Possible On the Invoice worksheet, incall E12, enter a whole number Data Valdarion that will alon numbers between 1001 and 5140 Select cel E 12. type 1001 Open the downloaded file edah08 grader_pc_Sos.xix. Save the file as 04ch08_grader pc Spa LastFirst, using your last and frst name. If a Security Warning message displays, alick tha Enable Editing button. On the invoice workshoot, in collC31, anter a text length Data Valdation that iimts the text length to less than or equal to 180 Enter the input message de Comments Enter the input message Comments may not be more than 180 characters. Enter the error Alert Title Error Enter the Error Alert Message Must be 180 characters or less On the Invoice worksheet, vetrece precedents and trace dependents to look at the formulas to make sure they are constructed properly. Fix any errors you find. 0.4 On the invoice worksheet use Evaluate Fomula to determine what is wrong with the formula in cel F93. Fix the formule. On the invoice worksheet, in cell D33, anter a data validation that will display data entry prompts. Enter the input message Tax rate Enter the input message All items and services require sales tax. On the Invoice worksheet, identify and correct the circular reference. Note, Mac users can skip this step. On the Invoice worksheet, add the Watch Window. Add walch for FS1, F33, and F95. Move the Watch Window to the top of the worksheet window. Select orl E15, typa 2. Notice that all the cells in the Watch Window changed Close the watch Window On the Invice worksheet, in cell E12, enviar a custom data validation Select the existing text, And then replace it with AND LEFT|E12,1 TEXTE6. "YYYYMMDD","&" "&IFEBO,TEXTIES, HHMM"),"&"FE100, VLOOKUP(E10 Therapists 2). 0.5 0.5 Enter a Deo mal data validation for all range E10-E30 to allow for decimals less than ar equal to 2 Enter the input message the Hours Enter the input message Enter the number of service hours. Enter a Warning alert. Enter the ste Invalid value Enter the Error massage The hours you entered exceed the maximum recommended. If necessary, add the Developer tab. Create a macro named ClearCalls with a shortcut key or C and the description To clear contents from cells The macra will clear the contents from cells ES, ES, E10, E12, and the cell ranges 31:32, 815 630, and E15:30. The macro will make cel E6 The active call after it is ran Save the workbook 8B 8 macro-enabled workbook with the filename e04ch08 grader pe Spa LastFirst In call Es, enter today's date. In oal EB anter2:30 PM. in coll F10, selectistas Christy, Test the Macro 0.4 On the Invoice worksheet, in cell El enter a Date validation that verifies the date is less than or us to TODAYO Enter the input it involo Dato Enter the input massage Enter the data in the following format: MM/DD/YYYY. Enter the Error alert title Error Enter the Error message box Future dates are not allowed Select cel E6, type TODAY! On the Invoice worksheet, in cell B16, choose Facial - Mud & Citrus. In cel E16, type 1. Sclect cel e 16 Create a Relative References Macro named Highlighiltace with the shortcut keyh and the description In highlight an invoice special. The macro will select cells B15:F15 and then change the font to Bold, size 14. Click call B16, and then select Hair Coloring. Test the macra. Created On: 07/06/2019 YO16_XL_CHOB_GRADER_PC_HW-Spa 14 Crased On 07/05/2019 YO16_XL_CHOB_GRADER_PC_HW - Se 14 INVOICE Turquoise Oasis Spa A Passion for Helping People Relax INVOICE #: DATE: TIME: THERAPIST: 3356 Hemmingway Circle Santa Fe, NM 87594 Phone: 505.555. SPA1 Fax: 505.555. SPAX ROOM: DESCRIPTION CHARGE/HOUR HOURS AMOUNT Mud Bath $ 100.00 150.00 1.00 $ 1.00 $ 100.00 150.00 Reflexology COMMENTS: SUBTOTAL $ 150.00 TAX RATE 6.50% #VALUE! Make all checks payable to Turquoise Oasis Spa. THANK YOU FOR YOUR BUSINESS! SALES TAX OTHER TOTAL #VALUE! Invoice Prices Products Therapists Macro + Return to invoice Product Facial - Mud & Citrus Facial - Steam Exfoliation Hair Coloring Hair Style & Cut - Men Hair Style & Cut - Women Makeup Consultation Manicure & Pedicure Package Manicure & Polish Pedicure & Polish Waxing - Body Waxing - Facial Aroma & Sound Therapy Package Aroma Therapy Body Detox Electrotherapy Body Detox Steam Therapy Massage - Deep Tissue Massage - Deep Tissue, Couples Massage - Fusion Massage - Fusion, Couples Massage - Shiatsu Mud Bath Reflexology Sound Therapy Steam Bath Tibetan Reiki Therapy Location Salon Salon Salon Salon Salon Salon Salon Salon Salon Salon Salon Spa Spa Spa Spa Spa Spa Spa Spa Spa Spa Spa Spa Spa Spa C D Price Unit 100.00 Each 75.00 Each 100.00 Each 35.00 Each 50.00 Each 1100.00 Each 70.00 Each 45.00 Each 45.00 Each 75.00 Each 25.00 Each 100.00 Hour 75.00 Hour 150.00 Hour 100.00 Hour 125.00 Hour 112.50 Hour 150.00 Hour 137.50 Hour 150.00 Hour 100.00 Hour 150.00 Hour 75.00 Hour 65.00 Hour 225.00 Hour Invoice Prices Products Therapists Macro B C D E F PRODUCTS Aroma & Sound Therapy Package Aroma Therapy Body Detox Electrotherapy Body Detox Steam Therapy Massage - Deep Tissue Massage - Deep Tissue, Couples Massage - Fusion Massage - Fusion, Couples Massage - Shiatsu Mud Bath Reflexology Sound Therapy Steam Bath Tibetan Reiki Therapy Invoice Prices Products Therapists Macro C D E THERAPISTS Istas, Christy Mault, Kendra Niese, Jason EMPLOYEE_NUMBER 3054 1477 5901 Invoice Prices Products Therapists A B C D E F G H Invoice Prices Products Therapists Macro
