Build the polished wizard with verification checkpoints and Definition of Done
Polish the Wizard
Open your Lesson 5 workbook and add validation, controls, and an audit panel.
Open the workbook you built in Lesson 5. If you need a fresh start, download the starter.
Required incoming state: Your Lesson 5 workbook with named ranges, adjusting entries block, verification checkpoint, Adjusted TB, and Run Close button.
Your finished workbook should have these new sections added to the Lesson 5 structure.
| New Section | Location | Purpose |
|---|---|---|
| Validation Rules | Next to each input cell | Flag invalid inputs before the close runs |
| Period Selector | Top of Inputs sheet | Dropdown to select close period without editing formulas |
| Audit Panel | Top-right of Inputs sheet | Shows period, inputs used, outputs, verification status |
| Updated Button Flow | Same button, enhanced macro | Checks validation before running, updates audit panel |
Add Validation Rules to Each Input
For each input cell (Supplies, PrepaidInsurance, etc.), add a Data Validation rule. Set minimum to 0, maximum to the account's unadjusted balance. Use conditional formatting to highlight violations in red.
Build the Period Selector
Create a dropdown (Data Validation → List) with options: "March", "April", "May". Link it to a named cell called SelectedPeriod. Your formulas should reference this cell, not hard-coded month names.
Create the Audit Panel
Build a small table at the top-right showing: Period (from SelectedPeriod), Total Adjustments (sum), Verification Status (OK/ERROR), and CloseStatus (Complete/Error). Use conditional formatting for green/red indicators.
Update the Button Flow
Modify the macro to check validation before running. If any validation flag is active, show a message and stop. If all clear, run the close and update the audit panel.
Your workbook is complete when all of the following are true:
- Validation rules on every input cell (minimum 5 rules)
- Period selector dropdown that drives formulas without editing them
- Audit panel showing period, total adjustments, verification status, and CloseStatus
- Button checks validation before running and refuses to proceed on errors
- Conditional formatting makes violations and status visible at a glance
- Workbook saved as .xlsm with all changes preserved
Validation not triggering? Make sure Data Validation is applied to the correct cell range. Select the cell → Data tab → Data Validation → check the settings.
Dropdown does not change anything? The dropdown must be linked to a cell that your formulas reference. Check that your formulas use the named cell, not a hard-coded value.
Audit panel shows wrong values? Check that each audit panel cell references the correct named range or calculation block. Use the Name Manager to verify references.
If none of these help, open the teacher reference workbook and compare your layout step by step.