Pulling data dynamically from standard project folders

KIS Solutions

We found a template for multiple projects that uses a standard folder setup to keep Reports, Project Plan and Dashboards for each project within its own project folder... allowing us to have the separate lists of tasks and RFI's, rollups organized for each project rather than a forever-growing list of entries from old projects that don't matter.

The limitation that Smartsheet seems to have is no ability to use formulas to dynamically specify a range. For example, if I have an "RFILog" in each project folder with a "critical" column, I would like to have some kind of rollup formula that looks at each Project folder (a dynamically built string based on the Project Name in each row in my main project intake table if the project status is ACTIVE?), opens each "RFILog" chart and does a simple count of critical path RFI's. Then roll that up into a single "RFI Status" column in the intake table that can be reflected in my main dashboard as "THIS Project Manager needs to go talk to THIS client! Project is falling behind!")

I seem to be able to do this somewhat manually by adding each "RFILog" sheet into a Report SHEET FILTER, but that's going to get out of hand after a couple dozen projects. We are an Engineering firm doing 5-10 small, highly similar projects a month, not 5-10 big projects a year. I just left Monday.com because it also can't handle multi-project management - just glorified task lists and one-project-at-a-time management. Smartsheet has taken it multiple steps farther and I really hope this is able to be our final solution.

Is there a way anybody knows of to traverse the project folders more dynamically? A native formula, a great data add-on, or some suggestions to reorganize my project structure to get a little more dynamic capability out of a folder-based structure? Seems like this is not really "the SmartSheet Way" or maybe it needs to be accompanied by a separate API service that can go beyond what built-in Cell Formulas can do.

Julie Fortney

We (and some companies we've partnered with) have experienced this same challenge. We have found master logs to be the best way to go.

Here's the setup we use:

1. Create one master log for each type (RFIs, Change Orders, etc.) including a column with a unique identifier for each project (usually project number)
   1. Bonus: you can have just one master form for each of these types, rather than separate forms per project.
2. Create reports in each project folder for each type that reference the master log(s), with a filter for that specific project. That makes it easy for the team to view just the entries for that project.
3. If you have Data Shuttle, you can dynamically update your master log project selection dropdowns as projects are added to your master project list. If not, with just 5-10 projects per month, it shouldn't be too bad to add/remove projects from your dropdowns.
4. To keep your master logs current, you could set up a column formula to flag project entries that don't appear on your active master project list (assuming you archive projects off the master project list), or projects that have a closed status, then set up a workflow to move those rows to archive logs.

Let me know if you have questions about this suggestion!

www.linkedin.com/in/juliefortney-pmp-smartsheetpartner-lssblackbelt

KIS Solutions

That's a great suggestion. I'll have to look into Data Shuttle. Sounds like I could start offloading some functions to my Odoo ERP system at various points as well.