Documentation Cooking Guide Created By Date And Purpo 909524

Documentationcookin Goodcreated Bydate Createdpurposeto Record Sale

Design and create an Excel workbook to manage data entry, validation, and reporting for Cookin’ Good products, including setting up named ranges, data validation rules, formulas, macros, and pivot table views, then test and finalize the workbook with appropriate macro buttons and formatting.

Paper For Above instruction

The project involves developing a comprehensive Excel workbook for Cookin’ Good, a company that sells specialized home cooking products through parties. The goal is to facilitate efficient data entry, validation, and reporting, enabling Cleo Benard, who manages sales data input, to streamline sales record management and analysis.

Initially, the user must open the provided Cookin.xlsx file, save it as "Cookin’ Good," and document their name and date in the designated Documentation sheet. This sets up the foundation for subsequent tasks, aligning with best practices in workbook organization.

The worksheet labeled "Sales Form" forms the core data entry interface. Specific named ranges should be created for cells C3 through C8, facilitating easier validation and macro references. The range E4:G15, containing product information, should be assigned the name ProductInfo for efficient lookup functions.

Next, data validation rules are implemented to ensure data integrity. In C3, only the numbers 1 through 5 (representing regions) are allowed, supplemented with input prompts and error alerts to guide the user. Similarly, C4 should restrict entries to the product IDs listed in E4:E15, with suitable validation messages. Cell C7, for units sold, must only accept whole numbers greater than zero, with appropriate validation prompts to avoid invalid inputs.

Formulas are entered to enhance automation and reduce manual entry errors. Cells C5 and C6 will contain VLOOKUP formulas that automatically populate the product name and price based on the product ID entered, and these should display blank if an error occurs. Cell C8 calculates the total sale value by multiplying units sold by the product price, using the IFERROR function to display blank if calculation errors occur.

Protection is configured on the Sales Form worksheet to prevent editing of unintended cells, allowing only C3, C4, and C7 to be modified. The worksheet is then protected to enforce these restrictions, ensuring data integrity while allowing necessary user input.

After setting up the form, test data entry by inputting a sample record: Region 1, Product ID "CW," Units Sold 8. This ensures validations, formulas, and protections work correctly.

The next step involves creating an "AddData" macro, assigned to the shortcut Ctrl+d. The macro copies the current data from C3:C8, then switches to the "Sales Record" worksheet. It uses relative references and End+ keys to find the last used row, then pastes the data transposed into the next available row, effectively appending new sales data. After appending, it refreshes the pivot table in the "Sales Table" worksheet to reflect new data, clears input cells C3, C4, and C7, and resets focus to C3. The macro recording is then stopped.

A button labeled "Transfer Sales Data" is inserted in C11:C12, linked to the AddData macro. This button facilitates quick data transfer and recording.

Subsequently, several additional macros are created: "ViewTable" (Ctrl+t), "ViewChart" (Ctrl+c), and "ViewForm" (Ctrl+f), each designed to navigate to the corresponding worksheet—"Sales Table," "Sales Chart," and "Sales Form," respectively. These macros streamline user navigation through the workbook.

Below row 13 in the Documentation worksheet, three macro buttons are created for quick access to these views, each assigned the appropriate macro with descriptive labels—enhancing usability and efficiency.

The "Sales Table" worksheet, which summarizes total product sales per region, is modified to display the values as percentages of column totals. This involves adjusting the pivot table's "Show Values As" setting to "% of Column Total," allowing for better analysis of regional sales contributions.

Two additional macros are developed to toggle the pivot table’s display style: "ShowAsPercent" (displays data as percentages) and "ShowAsNumber" (restores original values). These macros use the Range and Style commands to update the pivot table formatting dynamically.

Buttons linked to these macros are added below the pivot table, labeled appropriately to allow users to switch between percentage and number views easily.

Finally, the entire workbook is saved as "CG with Macros" to preserve macro functionality, and the file is closed with all testing and setup confirmed complete.

References

• Microsoft Support. (2023). Create and manage macros in Excel. https://support.microsoft.com
• Excel Easy. (2023). Data validation in Excel. https://www.excel-easy.com
• Chapple, M. (2020). Mastering pivot tables in Excel. Technical Publishing.
• Walkenbach, J. (2019). Excel VBA programming for Dummies. Wiley.
• Excel Campus. (2023). How to automate tasks with macros. https://www.excelcampus.com
• MyExcelOnline. (2023). Dynamic dashboards with PivotTables. https://www.myexcelonline.com
• Higgins, E. (2022). Data validation techniques in Excel. Journal of Data Management, 15(2), 45-53.
• Harvey, D. (2021). Effective Excel macro design. Packt Publishing.
• Microsoft Office Support. (2023). Protecting worksheets and workbooks. https://support.microsoft.com
• Gaskins, J. (2020). Visual Basic for Applications: Programming Excel macros. Packt Publishing.