Punch List vs Room-Ready Reporting

Topher Barnett
edited 08/24/23 in Smartsheet Basics

Hi All,

I've been wracking my brain on this and can't come up with a solution. Please help!

Current Use Case: Users (without SS accounts) use a form to fill in punch list items for rooms they are checking out. We need a separate punch list for each project to share with the builder in a public link. The punch list needs to be automatically generated via Control Center when the project manager checks a box in their separate project sheet. The punch list has several formulas based on check boxes to determine the status of the item. Some of these fields are locked so only the QA team can check them off.

The Problem: We are being asked to provide a room-ready list to show rooms that have open punch list items vs total number of rooms in scope. Each room may have more than one punch list item, or none at all.

Current Thoughts: I could add a checkbox in the form that says something like "No items to report for this room". That would at least give us all the rooms that were checked in one list. I can't think of a way to check the list of rooms in the punch list against a full list of rooms without having to enter a formula for a cross-sheet reference after the sheet has been generated. The idea here is that everything should be automated. I thought about pulling a report on all rooms checked, but then I can't use a formula to compare complete vs incomplete. I also thought about Dynamic View, but then I don't see a way to enter new lines, and it's not as user-friendly as the form.

Any help is greatly appreciated!