Smartsheet Resource Management is useful to check allocations at scale and to produce reports. But it is not really built for teams to check their allocations together and discuss how to best spread the load.
This used to be possible with what is now called Smartsheet Legacy Resource Management, which gave a great view of people's allocation over the next few days and weeks.
Since we have been robbed of this great tool, we can wallow on it all day, or we can do something about it. Lucky for us, in June 2024 Smartsheet released advanced reports for Resource Management, which lets you export a daily report from Resource Management straight into the core Smartsheet platform.
Using this alongside Data Shuttle, we can build a report that shows people's allocation for this week and next week. Technically we could show more weeks, but for operational teams delivering services daily, I have found that two weeks is enough of a time span.
Why bother building this rather than just giving your teams or managers access to Resource Management?
I see two main reasons.
The first is cost. Each access to the platform requires a licence, and view access is not the cheap one. The cheaper option is a "managed" resource who never gets platform access at all.
The second is simplicity. Resource Management can display people's allocation well enough, and it is easy to show a manager and their people, but the view can start to feel messy once someone is on more than five projects. Untangling that in the native view asks for a level of platform fluency you might not want to invest in this early.
1. Pull the raw data out of Resource Management
The Resource Management advanced report pushes an updated sheet into Smartsheet every day. If you want to see both this week's data and next week's data, you actually need two "Time & Fees in hours" reports.
The current week report shows data from Monday to Sunday (or Sunday to Saturday if you are in the US) for the current week, but there is no report that isolates "next week" data on its own, which is why the second report has to fill that gap.
The next 30 days report shows everything from today through the following 30 days. Exported on a Monday, that gives you two full weeks of data, but as soon as it refreshes on Tuesday, you lose Monday's data from that first week. That is exactly why you still need the current week report alongside it, to keep a complete two-week picture at any point during the week.
2. Reshape it with column formulas
In each source sheet, a helper column concatenates the project name and the team member name into one unique key, since RM has no shared ID across the two datasets. Other formulas isolate the week number, so you can identify "this week" and "next week" independently of the actual calendar date, and convert scheduled hours into a percentage of a full working week (for example, hours divided by 40).
3. Stitch the two sources into one final sheet with Data Shuttle
Data Shuttle workflows move the reshaped data into a single destination sheet, where INDEX/COLLECT formulas, matched on that unique project and team member key, pull in "this week %" and "next week %" side by side. A validity check then filters out any rows that snuck in from unrelated weeks.
Once that final sheet exists, it becomes a normal, editable Smartsheet asset. You can add RAG status columns, comments, or variance flags, then build a report on top, grouped by manager or team, with sums and filters, so managers get one clean, exportable, always-updating workload view instead of juggling RM's native reporting screens.
Pro tips
- The daily report from Resource Management exports once per day at the time you first created it, so pick you creation time well :)
- Make sure your formulas hold up when the year rolls over, from week 52 to week 1.
- Add an editable column so people can adjust this week's allocation per project, in case the theory does not match reality.
- Keep vacations and internal time provisioned in Resource Management on their own sheets, so you can flag them separately in the report.
- Scale the solution by adding fields so each person only sees their own team's data in the report. One single report can serve every team this way, so you are not left building and maintaining a separate report for each of them.
Want help setting this up for your own teams? Get in touch through the contact form below and let's talk about your Resource Management setup.
