Step

Instructions

Points Possible

1

Start Excel. Download and open the file named Excel_CH08_Prepare_SpaInvoice.xlsx. Grader will automatically add your last name to the start of the file name. Save the file to the location where you are storing your files.

0

2

Every formula has precedents, and some formulas may also have dependents. While you can always select a cell to see the cell references included in a formula, sometimes a visual cue is helpful to see how the formula works. When you choose to trace dependents and trace precedents, Excel puts arrows on the workbook to show you how the formula in the cell is constructed. These arrows make it easier to find errors than just by looking at the cell references in the formula. On the Invoice worksheet, select cell F31. Use trace precedents and trace dependents to look at the formulas to make sure they are constructed properly. Fix any errors you find.

0.8

3

Evaluating a formula walks you through the steps taken in calculating the result of a formula. You will use the Evaluate Formula to fix the error in cell F33. On the Invoice worksheet, use Evaluate Formula to determine what is wrong with the formula in cell F33. Fix the formula. Note, Mac users, evaluate the formula in the worksheet itself and correct as necessary.

1.2

4

A circular reference is an error a majority of the time in a worksheet indicating a single formula that references itself or multiple formulas that reference each other. The Invoice worksheet has a circular reference. On the Invoice worksheet, identify and correct the circular reference.

1

5

To set up the invoice so that the rows at the top of the invoice don't scroll off the screen when entering data, you will set Freeze Panes. In cell B15, set Freeze Panes.

1

6

You will create a list validation on the Therapist field to only allow the entry from the list of therapists found on the Therapists worksheet. On the Invoice worksheet, select cell E10 and add a list Data Validation using cell range A2:A4 from the Therapists 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 Invalid Name Enter the Error message The name you entered is not a valid name. Select a name from the list available. Click or press the filter arrow next to cell E10, and then select Istas, Christy.

1.4

7

The maximum number of hours each spa service is offered is 2. Therefore, you will create a validation to limit the hours of each service to 2. 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.

1

8

In the Spa Invoice workbook, the date entered in cell E6 should be restricted to the current date or earlier. Thus, invoices may not be dated with a future date. To ensure accuracy of date entry, you will create a Date validation. 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 MM/DD/YYYY format. Enter the Error alert title Invalid Date Enter the Error message box Future dates are not allowed to be invoiced. Select cell E6, type =TODAY()

1.2

9

You will add validation criteria to cell E8 to ensure that only a time value between 10:00 AM and 6:30 PM, the spa hours, can be entered. On the Invoice worksheet, in cell E8, enter a Time data validation that will limit entry to start time of 10:00 AM and end time 6: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 Invalid Time Enter the Error message The time entered must be between 10:00 AM and 6:30 PM. Select cell E8, type 3:00 PM

1

10

Text length validation can also prevent cell data from becoming too long. On the Invoice worksheet, the length of the comments in cell C31 is limited to 200 characters. On the Invoice worksheet, in cell C31, enter a text length Data Validation that limits the text length to less than or equal to 200 characters. Enter the Error Alert Title Comments Enter the Error Alert message Comments may not be more than 200 characters.

1.2

11

In the Spa Invoice workbook, you will create a prompt so that when the user clicks on or presses the Tax Rate in cell D33, a prompt appears with more information about the tax rate. 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.

1

12

In the Spa Invoice workbook, you will change the data validation for the room number that only specifies a minimum and maximum value and will create instead a custom validation rule that will allow only the following numbers to be entered in cell E12 for the room number: 1001-1140, 2001-2140, 3001-3140, 4001-4140, and 5001-5140. 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 Alert Title Error Enter the Error message Invalid room number. In cell E12, type 3120.

2

13

Next, you will create this codification scheme in a formula that will automatically generate the invoice number based on the data entered in the invoice. In cell E4, type =IF(E6>0,TEXT(E6,”YYYYMMDD”),””)&” “&IF(E8>0,TEXT(E8,”HHMM”),””)&” “&IF(E10>0,VLOOKUP(E10,Therapists,2,FALSE),””)

2

14

A macro is often used to automate repetitive tasks. You will record an absolute macro to clear the current data, but leave all the formulas necessary for the invoice to calculate correctly. If necessary, add the Developer tab. Make cell E4 active. Create a macro named ClearCells with a shortcut key of C and the description To clear contents from cells. (Use the SHIFT key to ensure the shortcut key is a capital C.) Record the macro to clear the contents from cells E6, E8, E10, E12, B15:B30, C31 and E15:E30. Make cell E6 the active cell before you stop recording the Macro. In cell E6, enter today’s date. In cell E8, enter 2:30 PM. Note: The book will show 3:00 PM, but it doesn't matter – you will clear the macro. In cell E10, select Istas, Christy. Test the Macro by using the Shortcut key.

0

15

A macro button can make it easy for a user to run a macro with little or no knowledge of how a macro works. You will create a Macro button for the Clear Contents macro. On the Invoice worksheet, add a Button (Form Control) in the top left corner of cell F5, and then drag to the bottom right corner of cell G6 to draw the button. Assign the ClearCells macro to the button. Edit the button text to Clear Invoice Test the button.

0.8

16

When a customer is charged a special price, the spa likes to highlight that invoice item. In this exercise, you will create a relative macro to highlight a row in the invoice. On the Invoice worksheet, in cell B15, choose Facial – Mud & Citrus. In cell E15, type 1 Select cell B15. Create a Relative References Macro named HighlightItem with the shortcut key H and the description To highlight an invoice special. Record the macro to select cells B15:F15 and then change the font to Bold, size 14. To test the macro, in cell B16, select Hair Coloring. Test the macro by using the shortcut keys.

0

17

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. Select cell B15 and then stop recording the macro.

0

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”).Select 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. Select cell B15, and select Facial – Mud & Citrus. Run the HighlightItem macro. Click or press the Clear Invoice button. If necessary, on the Home tab, verify that the formatting in row 15 was changed back to size 9, not bold.

0

19

On the Invoice worksheet, if necessary, display the Developer tab. In the Code group, click or press Macros. In the Macro dialog box, in the Macro name list, select ClearCells, and then select Edit. A 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. Display the Macro worksheet, 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. Display the Macro worksheet, 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. Display the Macro worksheet, and paste that code beginning in cell M1.

0

20

You want to be able to move quickly from worksheet to worksheet. Therefore, you will add hyperlinks between the Invoice worksheet and the Prices worksheets. 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

0.8

21

Hide the Therapists worksheet.

0.8

22

On the Invoice worksheet, unlock cells E6, E8, E10, E12, D14, C31, F34, and cell ranges B15:B30 and E15:E30. Select Format Cells. Protect the Invoice worksheet and do not allow for locked cells to be selected. Allow formatting Press HOME.

0.8

23

On the Invoice worksheet, remove the Gridlines.

0.8

24

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

1.2

25

On the Invoice worksheet, hide the horizontal scroll bar. Note, This feature is not available on a Mac. Mac users, skip this step.

0

26

Protect the workbook structure. Do not add a password.

0

27

Mark the Workbook as Final. Note, This feature is not available on a Mac. Mac users, skip this step.

0

28

Save and close Excel_CH08_Prepare_SpaInvoice.xlsx. Exit Excel. Submit the file as directed. Note, Be sure you are not submitting a macro-enabled workbook or a workbook that includes an encryption password.

0

Total Points

20