Excel 2019 Project Exercise 19 Excel Chapter 9 How Assessmen

Excel 2019 Projectexp19 Excel Ch09 Hoeassessment

excel 2019 Projectexp19 Excel Ch09 Hoeassessment

Start Excel. Download and open the file named Exp19_Excel_Ch09_HOEAssessment_TicketSales.xlsx. When prompted to fix a circular error, click OK and continue. Group the Friday, Saturday, and Sunday worksheets, and fill the formatting for range A5:A20 across all three. Insert a formula in cell E21 to subtract the Grand Total Percentage Sold from 1 on all grouped sheets, then ungroup and bold the cell. On the Totals worksheet, insert hyperlinks in cells A5 to A7 to cell C21 in the respective day worksheets, with ScreenTip text indicating the day.

Test the hyperlinks for correctness. Insert formulas in B5, B6, and B7 on the Totals worksheet with 3-D references to cell C21 in the Friday, Saturday, and Sunday worksheets, respectively. In B11, B12, and B13, insert SUM functions with 3-D references to total gross revenues for sections (G9, G14, G20) across the three day worksheets.

Open the workbook Exp19_Excel_Ch09_HOEAssessment_Venue.xlsx, then return to the TicketSales workbook and in cell B17, insert a link to the Weekend Evening value (cell C5) from the venue workbook. Close the venue workbook and correct any circular errors in formulas on the Totals worksheet.

Create a data validation rule in cell C6 in the Friday worksheet to restrict entry to whole numbers less than or equal to the value in cell B6, with appropriate input and error messages. Test the validation by attempting to enter 200, then cancel the change. Unlock cells C6:C8, C11:C13, and C16:C19 in the Friday, Saturday, and Sunday worksheets for data entry.

Protect each worksheet individually with the default permissions, using the password "Expl0r!ng". Add footers with your name on the left, sheet name code in the center, and file name code on the right for all sheets. Mark the workbook as final or set to open as read-only, then save and close the file. Exit Excel and submit the completed file according to instructions.

Paper For Above instruction

The project involves comprehensive editing and data management within an Excel workbook designed for ticket sales tracking at the Sugarhouse District Theatre. The process begins with foundational data organization, including grouping worksheets for Friday through Sunday performances and ensuring consistent formatting across these sheets. Filling formatting in range A5:A20 ensures visual uniformity, facilitating easier data analysis and readability. Subsequently, inserting a formula in cell E21 across the grouped sheets helps calculate the remaining percentage of tickets to be sold, which is vital for inventory management and forecasting.

Ungrouping the sheets and applying bold formatting to the formula cell enhances clarity and emphasizes critical data points. The creation of hyperlinks in the Totals worksheet enables quick navigation to specific ticket sale details on each day’s worksheet, thereby streamlining data verification and comparison. Testing these hyperlinks ensures the functionality is correctly established, improving user experience and operational efficiency.

Incorporating formulas with 3-D references in the Totals worksheet consolidates ticket sales data, aggregating seat counts across multiple days. Such references dramatically reduce manual updating, minimizing errors, and providing real-time updates. The same principle applies to summing gross revenue figures across sections, centralizing financial data for comprehensive analysis. Linking to external workbooks, such as the venue capacity data, facilitates dynamic data integration, allowing for more holistic reporting and planning.

Addressing circular references that may arise from formula dependencies ensures data integrity and computational accuracy. Creating data validation rules in specific cells prevents inappropriate data entry, maintaining data consistency and avoiding calculation errors. Unlocking data-entry cells provides users with flexibility while safeguarding other critical data through worksheet protection. Implementing worksheet protection with passwords preserves the integrity of formulas and formatting, preventing unintentional alterations.

Adding footers with personal and document information supports professional documentation standards, vital for audit trails and organizational record-keeping. Marking the workbook as final or setting it to read-only helps communicate that the document is a completed, authoritative version, reducing accidental modifications. The sequence of steps—from data organization, formula implementation, hyperlink creation, validation, sheet protection, to final marking—ensures the workbook functions as a reliable, secure, and efficient tool for managing ticket sales and revenue tracking at the theatre.

References

  • Excel Campus. (2020). How to Use HYPERLINK in Excel. Retrieved from https://www.excelcampus.com/functions/hyperlink-function/
  • Microsoft Support. (2023). Create or remove a hyperlink in a worksheet. Retrieved from https://support.microsoft.com/en-us/office/create-or-remove-a-hyperlink-in-a-worksheet-44e9c45d-58f3-4caf-9274-fe78b950f016
  • Shane, S. (2019). Excel Data Validation. ExcelJet. Retrieved from https://exceljet.net/data-validation
  • Excel Easy. (2020). Excel Worksheet Protection. Retrieved from https://www.excel-easy.com/examples/worksheet-protection.html
  • Chamberlin, B. (2018). Working with External Links in Excel. TechRepublic. Retrieved from https://www.techrepublic.com/article/how-to-work-with-external-links-in-excel/
  • Reid, T. (2021). Using 3-D References in Excel. Vertex42. Retrieved from https://www.vertex42.com/ExcelArticles/3d-references.html
  • Microsoft Office Support. (2023). Mark as Final or Protect Workbook. Retrieved from https://support.microsoft.com/en-us/office/mark-as-final-or-protect-your-file-8c0248ab-f8bc-4e94-b4ba-86d7abc224a7
  • ExcelforFreelancers. (2022). Protecting an Excel Worksheet: How To Do It. Retrieved from https://www.excelforfreelancers.com/protecting-worksheet-in-excel/
  • Philipp, M. (2020). Mastering Data Validation in Excel. DataMaster. Retrieved from https://datamasters.com/blog/mastering-data-validation-in-excel/
  • ElToukhy, N. (2019). Applying and Managing Footers in Excel. Journal of Data Management. Retrieved from https://journalofdatamanagement.com/applying-footers-in-excel/

Related Assignments