Please help.. (Below, there is attachment for excel)

Step

Instructions

1

Open the downloaded file e04ch08_grader_pc_Spa.xlsx. Save the file as e04ch08_grader_pc_Spa_LastFirst, using your last and first name. If a Security Warning message displays, click the Enable Editing button.

2

On the Invoice worksheet, use trace precedents and trace dependents to look at the formulas to make sure they are constructed properly. Fix any errors you find.

3

On the Invoice worksheet, use Evaluate Formula to determine what is wrong with the formula in cell F33. Fix the formula.

4

On the Invoice worksheet, identify and correct the circular reference.

5

Note, Mac users can skip this step. On the Invoice worksheet, add the Watch Window. Add watch for cellsF31, F33, and F35.

Move the Watch Window to the top of the worksheet window.

Select cell E15, type 2. Notice that all the cells in the Watch Window changed.

Close the Watch Window.

6

On the Invoice worksheet, select cell E10 and add a list Data Validation using cell range A2:A4 from the Therapist worksheet as the source.

Enter the Input message title Select a therapist

Enter the Input message Select the therapist who delivered the services listed.

Enter the Error Alert title Not a valid name

Enter the Error message The name you entered is not a valid name. Please select a name from the list.

Click the filter arrow next to cell E10, and then select Istas, Christy.

7

Enter a Decimal data validation for cell range E15:E30 to allow for decimals less than or equal to 2.

Enter the Input message title Hours

Enter the Input message Enter the number of service hours.

Enter a Warning alert. Enter the title Invalid value

Enter the Error message The hours you entered exceed the maximum recommended.

8

On the Invoice worksheet, in cell E6 enter a Date validation that verifies the date is less than or equal to =TODAY()

Enter the Input title Invoice Date

Enter the Input message Enter the date in the following format: MM/DD/YYYY.

Enter the Error alert title Error

Enter the Error message box Future dates are not allowed.

Select cell E6, type =TODAY()

9

On the Invoice worksheet, in cell E8, enter a Time data validation that will limit entry to start time of 8:00 AM and end time 4:30 PM.

Enter the Input title, Appointment time

Enter the Input message Enter the appointment time as HH:MM AM/PM.

Enter the Error Alert Title Error

Enter the Error message The time must be between 8:00 AM and 4:30 PM.

Select cell E8, type 2:30 PM

10

On the Invoice worksheet, in cell E12, enter a whole number Data Validation that will allow numbers between 1001 and 5140

Select cell E12, type 1001

11

On the Invoice worksheet, in cell C31, enter a text length Data Validation that limits the text length to less than or equal to 180

Enter the Input message title 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.

12

On the Invoice worksheet, in cell D33, enter 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.

13

On the Invoice worksheet, in cell E12, enter a custom data validation. Select the existing text, and then replace it with

=AND(LEFT(E12,1)<="5",LEFT(E12,1)>="1",RIGHT(E12,3)>="001",RIGHT(E12,3)<="140",LEN(E12)=4)

Enter the Input title Room number

Enter the Input message Enter the 4-digit room number.

Enter the Error Alter title Error

Enter the Error message You must enter a 4-digit room number.

In cell E12, type 3120.

14

In cell E4, type =IF(E6>0,TEXT(E6,"YYYYMMDD"),"")&" "&IF(E8>0,TEXT(E8,"HHMM"),"")&" "&IF(E10>0,VLOOKUP(E10,Therapists,2),"")

15

If necessary, add the Developer tab. CreateA macro named ClearCells with a shortcut key of C and the description To clear contents from cells.

The macro will clear the contents from cells E6, E8, E10, E12, and the cell ranges C31:D32, B15:B30, and E15:E30. The macro will make cell E6 the active cell after it is ran.

Save the workbook as a macro-enabled workbook with the filename

e04ch08_grader_pc_Spa_LastFirst

In cell E6, enter today's date. In cell E8, enter 2:30 PM. In cell E10, select Istas, Christy. Test the Macro.

16

On the Invoice worksheet, in cell B15, choose Facial - Mud & Citrus. In cell E15, type 1.

Select cell B15.

CreateA Relative References Macro named HighlightItem with the shortcut key h and the description To highlight an invoice special. The macro will select cells B15:F15 and then change the font to Bold, size 14.

Click cell B16, and then select Hair Coloring. Test the macro.

17

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 ClearCells macro to the button. Edit the button text to Clear Invoice.

Test the button.

CreateAn absolute reference macro named ClearFormatting with the shortcut key k and the description To clear special highlighting from the invoice. The macro will select thecell range B15:F30, remove the bold font, change the font size back to 9, and then click cell B15.

18

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

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 HighlightItem 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 ClearFormatting 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 screentip, Go to Prices worksheet

On the Prices worksheet, in cell F2, insert a hyperlink to cell D14 on the Invoice worksheet. Add the ScreenTip Return to Invoice worksheet

21

Hide all worksheets except Invoice.

22

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.

23

On the Invoice worksheet, remove the Gridlines.

24

Unprotect the Invoice worksheet. On the Invoice worksheet, hide all formulas, and then protect the sheet again.

25

On the Invoice worksheet, hide the horizontal scroll bar.

26

Protect the workbook structure. Do not add a password.

27

Close the workbook, exit Excel, and then submit your file as directed by your instructor.

https://drive.google.com/file/d/1pcUoEXYKmK36CqsjZF5bRnjM3mUuas2R/view