Use six assignments as the denominator
This original fictional example assigns Orientation and QA Practice to each of three demonstration employees. All identities and evidence references are invented; none is a certification record.
| Assignment ID | Employee | Course | Recorded state | Completion evidence |
|---|---|---|---|---|
| A-ORI-01 | Employee A | Orientation | Completed in demonstration | DEMO-E01 |
| B-ORI-01 | Employee B | Orientation | Completed in demonstration | DEMO-E02 |
| C-ORI-01 | Employee C | Orientation | Completed in demonstration | DEMO-E03 |
| A-QA-01 | Employee A | QA Practice | Scheduled | [NOT YET SUPPLIED] |
| B-QA-01 | Employee B | QA Practice | Attendance only | [COMPLETION CHECK NEEDED] |
| C-QA-01 | Employee C | QA Practice | Not started | [NOT YET SUPPLIED] |
Three of six assigned items have supplied completion evidence: 3/6 = 50% of assignments. That is not 50% of employees qualified. Every fictional employee has one orientation completion, and no QA completion is evidenced.
The Smartsheet training-matrix descriptions connect personnel or roles with training status and notes. This dashboard adds an original evidence-and-denominator review. It does not reproduce a workbook or establish your employer's qualification policy.
Build the source before the summary
In Excel, use an Assignments table with assignment ID, employee ID, course/version, assigned date, due date if supplied, state, required completion evidence, actual evidence reference, and next owner action. Keep the rules defining completion in a separate reference section.
For the demo, the summary is a count of evidence-backed completed assignments divided by all six assigned rows. Do not remove unstarted rows from the denominator or count attendance as completion merely because the course name matches.
Check duplicate IDs and repeated employee/course/version assignments before calculating. A missing evidence reference on a completed row should trigger review. If a course allows attendance as sufficient evidence, use the employer's actual supplied rule; do not infer that policy from this example.
Keep filtered and overall figures distinct
Selecting Orientation gives three evidenced completions out of three Orientation assignments. Selecting QA Practice gives zero out of three. Show the selected-course denominator beside that view and retain the overall six-row basis separately.
A dashboard that changes to 100% when Orientation is selected must label it “Orientation assignments,” not “All training.” Likewise, a personnel filter should not quietly redefine the employer's overall completion figure.
For B-QA-01, the next action is to obtain the required completion check, not mark Employee B qualified. For A-QA-01, confirm whether the scheduled session occurred and what evidence it produced. C-QA-01 needs a planning follow-up under the actual assignment rules.
Prompt for the Excel specification
Specify an original employee-training dashboard for Excel in US English.
Assignment rows with employee/course/version IDs: [inputs]
Required completion evidence and employer policy: [inputs]
Actual states, dates, and evidence references: [inputs]
Review date and next-action owners: [inputs]
Required workbook or employer record format: [format]
Define a source Assignments table and a summary with explicit numerator/denominator. Preserve scheduled, attendance-only, not-started, and evidence-backed completed states. Show course/employee drilldown while labeling filtered scope separately from overall totals. Flag duplicate assignments, missing evidence, and contradictions before calculations. Provide formulas only against supplied field names, explaining their eligibility rules rather than treating nonblank text as completion.
Do not invent employee identities, evidence, qualifications, certifications, attendance, or policy. Use fictional data for a public example and [NEEDED: input] for missing records. Retain Excel or the employer's required system as authority. Any optional HTML snapshot is sanitized and dated, without live import, tracking, certificate issue, or tested-formula claims.
Review the three QA rows against the required completion rule, record the next action, and update the actual evidence in the maintained workbook. Keep the 50% statement attached to its six-assignment denominator until those source rows change.
Optional: inspect the evidence snapshot in NOS
NOS can save and render a prepared HTML snapshot using fictional assignment records. Use the comparison to inspect the attendance-only item and the denominator; retain actual personnel, assessment, and qualification records in the authorized system.
