Description
Open e09c1Theatre and save it as e09c1Theatre_LastFirst.
Group the Template, Friday, Saturday, and Sunday worksheets. Fill the formatting for the range A5:A20.
Keep the worksheets grouped. Click cell E20, enter a formula to subtract the Grand Total Percentage Sold from 1. Apply bold to the cell.
Keep the worksheets grouped. Edit cell J5 by enclosing the AVERAGEIF function within an IFERROR function. The value_if_error argument should display the text Missing data. Copy the function to the range J6:J7 to eliminate the #DIV/0! errors displayed. Fill without formatting to preserve the existing borders. Ungroup the worksheets.
Hide the Template worksheet.
Create Hyperlinks
The Totals worksheet contains summary data. You will insert a hyperlink to the number of seats sold in each respective worksheet.
Display the Totals worksheet. Insert a hyperlink in cell A5 that links to cell C20 in the Friday worksheet. Include the ScreenTip text Friday Seats Sold. Test the hyperlink to make sure it is correct.
Insert a hyperlink in cell A6 that links to cell C20 in the Saturday worksheet. Include the ScreenTip text Saturday Seats Sold. Test the hyperlink to make sure it is correct.
Insert a hyperlink in cell A7 that links to cell C20 in the Sunday worksheet. Include the ScreenTip text Sunday Seats Sold. Test the hyperlink to make sure it is correct.
Create 3-D References and Correct an Error
Although you created hyperlinks to the number of seats sold each day, you want to insert a formula to retrieve the values in the Total worksheet. The second section of the Totals worksheet is designed to provide a summary of gross revenue by seating section. You will insert a function with a 3-D reference to calculate total gross revenue by section. Finally, you will use the Error Checking tool to find and correct an error in a formula.
Click cell B5, insert a formula with a 3-D reference to cell C20 in the Friday worksheet.
Click cell B6, insert a formula with a 3-D reference to cell C20 in the Saturday worksheet.
Click cell B7, insert a formula with a 3-D reference to cell C20 in the Sunday worksheet.
Click cell B11, insert the SUM function with a 3-D reference to calculate the total Orchestra Front gross revenue (cell G9) for the Friday, Saturday, and Sunday worksheets.
Click cell B12, insert the SUM function with a 3-D reference to calculate the total Orchestra Back gross revenue (cell G14) for the Friday, Saturday, and Sunday worksheets.
Click cell B13, insert the SUM function with a 3-D reference to calculate the total Balcony gross revenue (cell G19) for the Friday, Saturday, and Sunday worksheets.
Use the error checking tool to identify the location of a circular reference and then correct the error.
Consolidate Data
Although the Totals worksheet contains specified aggregated data, such as the number of seats sold and gross revenue by section, you want to create another summary that consolidates data from the three daily worksheets. In the Details worksheet, you will list the detailed section seating and gross revenue by day for those seating sections.
Click cell A2 in the Details worksheet. Use the Consolidate tool to consolidate data:
range I10:J19 in the Friday worksheet
range I10:J19 in the Saturday worksheet
range I10:J19 in the Sunday worksheet
use top row and left column labels
Select the range B2:D2, apply bold, and center horizontal alignment.
Link Workbooks and Correct an Error
The third section of the Totals worksheet needs to link to a value in another workbook. That value will be used to perform a calculation for total fees.
Open e09c1TheatreVenue and save it as e09c1TheatreVenue_LastFirst. Arrange the two open workbooks vertically to display both workbooks at the same time.
Click cell B17 in the Totals worksheet on the e09c1Theatre_LastFirst worksheet. Insert a link to the Weekend Evening value (cell C5) in the TheatreVenue worksheet. Close the TheatreVenue workbook.
Click cell B19 and multiply the weekend evening fee by the number of evening performances.
Data Validation
You want to create a validation rule to prevent the user from entering too many seats sold. After creating the validation, you will use Paste Special to copy the validation to other parts of the Friday worksheet.
Select the range C6:C8 in the Friday worksheet and create a data validation rule with these specifications:
Allow whole numbers less than or equal to =B6.
Enter the input message title: Seats Sold.
Enter the input message: Enter the number of seats sold. (including the period).
Select the Stop error alert style.
Enter the error alert title: Invalid Number.
Enter the error message: The number of seats sold cannot exceed the total seats in the section. (including the period).
Test the validation by trying to enter 360 in cell C7. Cancel when the error message displays.
Copy the range C6:C8. Select the range C11:C13. Use the Paste Special Validation option to copy the validation settings. Select the range C16:C18 and paste the validation again.
Data Protection
After creating, designing, and formatting the worksheets, you want to protect them from authorized changes. Therefore, you will unlock cells to allow data entry in specific cells and then protect the worksheets. Finally, you will mark the file as final.
Group the Friday, Saturday, and Sunday worksheets. Select the ranges C6:C8, C11:C13, and C16:C18 and unlock these cells. Ungroup the worksheets.
Protect the Friday, Saturday, and Sunday sheets using the default settings with the password Expl0r!ng. You must protect each worksheet individually.
Mark the workbook as final. Close the workbook and exit Excel.
Open File Explorer, select e09c1Theatre_LastFirst and e09c1TheatreVenue_LastFirst and compress them into a folder named e09c1TheatreFiles_LastFirst. Based on your instructor’s directions, submit: e09c1TheatreFiles_LastFirst.zip
