Need recommendation for project intake and tracking within Smartsheet

JPJPJP

We're looking to move our project tracker from Excel/Google Sheets to Smartsheet. But I'm not really sure how to implement our potential solution into Smartsheet. We currently do a lot of manually entry in a spreadsheet, but looking for a better solution.

For the new process, we want this for intake (I already know how to do this):

1. The user submits a project request (with full details - approx. 25 fields) via Smartsheet Form.
2. The data gets stored in an intake sheet and sends an email notification to us for approval.

From here, I'm not sure how to go about this due to concern that of hitting the 500,000 cell limit.

In our current Excel sheet, we manually enter in the important high-level data for each request and we do not store all 25 fields in the sheet. We're currently around 5k records in the tracker sheet and grows quickly (we keep it all in one sheet so stakeholders can easily track).

However, in our new tracker in Smartsheet we want the following:

• Once we approve the email notification, the row from the Intake sheet should move to the Tracker sheet (only the important columns, not all 25 fields)
• We want to manually add the subtasks under each project in the Tracker sheet (or they can be added into a separate 'Deliverables' sheet if there's a way to link them so we can easily interact with the subtasks across various projects in one Card view). Some projects only have 1 subtask, but some projects can also have 20+ tasks, which also adds to the concern that it can easily reach the max cell limit if we don't archive the old 'Completed' project requests.
• Once a project is marked as 'Invoiced' (checkbox column), it can either remain in the sheet or move to an Archive sheet. We don't necessarily need the child subtasks archived, but it would be great if it still can be.

And that's about it. It's not too complicated of an ask, our main concern is we do get a lot of requests and we do collect a lot of information from our form. So when requests are invoiced, it does take up a lot of rows, but we do need to keep them for archive purposes and forecasting.

Would love to hear any recommendations or if anyone else has Request Trackers with deliverables/subtasks in smartsheet as well including archiving.

Andrée Starå

Hi @JPJPJP

I hope you're well and safe!

There are multiple ways to structure this.

Have you looked into the Premium App, Control Center? Is that an option?

At a glance, if Control Center isn't an option, I would probably structure it something like this.

• Connect the tracker sheet to the Intake sheet with cross-sheet formulas
• The tracker sheet can either be set up with multiple groups (Parent & Children) or added for each project
• Add a Project ID found on the Intake sheet to collect the relevant information to the Tracker sheet.
• Archive if needed by checking an Archive box on the Parent row. All the children will also be Archived.

Make sense? What do you think?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

JPJPJP

Sorry for the delay in response. Unfortunately Control Center is not an option for us.

Question - I noticed there's a 100,000 cell limit for cross-sheet formulas. Is that per sheet or per account?

Here's my thoughts on the process, do you think this is do-able?

• Intake form sends ALL fields to "Intake" sheet
• An approval notification gets sent to my team email
• If approved, move row (only the first few columns) to the "Tracker" sheet and move the remaining columns to a second "Deliverables" sheet. (Is this possible?? to move select columns to 2 separate sheets and link them both with the Project ID?)
• If rejected, delete row from "Intake" sheet.
• In the "Tracker" sheet, when the "Invoiced" column is changed to Yes:
• --- Move the row to an "Archive" sheet.
• --- Delete the row from the "Deliverables" sheet (or move it to a "Deliverables Archive" sheet, we're still deciding if we need to archive these or not).

Also, another question - are the Reports and Dashboards features able to consolidate all the rows from both the Tracker and Archive sheets into single reporting? Currently in Google Sheets, every project request since the beginning are in the same sheet, so it's easy for us to do reporting since it's all in one sheet. But if we're using multiple sheets in Smartsheet with the same columns, I'm hoping this won't effect the reporting?

Andrée Starå

Hi @JPJPJP

No worries! My turn! 😉

I hope you're well and safe!

How did it go? Did you manage to get something set up?

I hope that helps!

Have a fantastic weekend & Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD