Use one common period and one availability record
This original illustrative comparison uses the week beginning October 19, 2026. Reviewer R1 has eight available review hours for that week. Project Cedar requests six hours and Project Birch requests five. Those are fictional planning inputs, not observed staff productivity.
| ProjectID | RequestID | ReviewerID | WeekStart | RequestedHours | DecisionStatus |
|---|---|---|---|---|---|
| Cedar | Review01 | R1 | 2026-10-19 | 6 | Priority to confirm |
| Birch | Review01 | R1 | 2026-10-19 | 5 | Priority to confirm |
Use Cedar/Review01 and Birch/Review01 as composite identities. A request code can recur across projects without becoming the same source row. Keep R1's eight hours in a separate availability table with one row for R1 and the week; do not repeat and sum eight hours under each project.
The Smartsheet multi-project template descriptions include task identities, owners, dates, statuses, dashboard source data, and dependencies. The workbook structure and reviewer conflict here are original; they are not a downloaded vendor workbook or a claim about its formulas.
Build three simple workbook sections
Requests: One row per project/request/reviewer/week, with hours, source reference, prerequisite, and decision state.
Availability: One row per reviewer/week, with available hours and the person/date confirming the allowance.
Review: A selected reviewer/week, matching source rows, requested total, availability, gap, and unresolved decision. Keep both Cedar and Birch visible when inspecting either project so a filter cannot hide the competing request.
Before summarizing, check for duplicated request identities, repeated availability rows, mismatched weeks, text where hours should be numbers, and blank hours. An unknown request is not zero; a total excluding it should be marked incomplete.
Calculate the conflict transparently
For a clean illustrative Requests table named Requests, with the columns shown above, enter the selected reviewer in B2 and a real Excel week-start date in B3. The requested-hours calculation is:
=SUMIFS(Requests[RequestedHours],Requests[ReviewerID],B2,Requests[WeekStart],B3)
The Microsoft SUMIFS documentation describes summing values that meet multiple criteria. Here the two criteria keep reviewer and week aligned. Use actual dates in the source table, not visually similar text dates.
For these checked sample rows, the total is 6 + 5 = 11 hours. Against the single eight-hour allowance, the gap is 3 hours. In the review section, label the gap “Requested minus available”; keep negative values distinguishable from a shortage if you use the same formula for other cases.
This formula is a specification to implement and verify in Excel. It does not validate duplicate identities or prove every source entry is complete.
Keep progress denominators separate
If Cedar reports 2 of 4 tracked tasks complete and Birch reports 1 of 5, show 2/4 = 50% and 1/5 = 20% separately, with the task-count basis. Do not call their simple average, 35%, portfolio completion. It gives the projects equal weight without establishing a common measure of work or value.
The reviewer decision is separate from those percentages: which request changes sequence, scope, or timing, and who has authority to agree it? Leave priority and revised allocation unconfirmed until the actual decision. If Cedar needs an input from Birch, name the prerequisite by its project/request identity; do not hide it in a generic risk color.
Copy an Excel-oriented planning prompt
Specify an original multiple-project review dashboard for Excel in US English.
Project/task/request rows with stable IDs: [paste rows]
Reviewer availability by matching week/unit: [paste records]
Cross-project prerequisites: [inputs]
Project completion measures and denominators: [inputs]
Actual decision owners and snapshot date: [inputs or unknown]
Design Requests, Availability, and Review sections. Use unique project-plus-request IDs, one availability row per reviewer/week, compatible period/unit sums, and visible source-row detail beside each conflict. Provide formulas against the exact supplied table/column names and flag duplicates, blanks, nonnumeric hours, text-date mismatches, and unsupported availability. Unknown demand must not become zero.
Show demand minus availability without choosing priorities or inventing approval. Keep each completion denominator separate; do not average unrelated percentages into portfolio completion. Include prerequisite identity and decision-to-confirm fields. Mark gaps [NEEDED: input]. Retain Excel as the editable authority; label any optional HTML copy with its snapshot date, without import/sync/export or tested-formula claims.
Check the two request rows and the single availability record, then ask the authorized owner to resolve the three-hour conflict. Record the actual decision in Excel and recompute the matching week. The dashboard is useful when its summary leads back to the requests that need a choice.
Optional: read a dated comparison snapshot in NOS
If you prepare an HTML copy of this reviewer/week comparison, NOS can save and render it for review. Keep its snapshot date visible and leave workbook rows, formulas, allocations, and approvals in Excel. The copy represents the prepared comparison, not a live connection to the workbook.
