Can I take cells from a horizontal row and add them to a new sheet as independent rows?
Here is the situation:
I am tasked with helping a shared resource team better understand value stream and efficiency metrics. All of their project data comes in to a master sheet where each row contains various resource assignments (by role) and start/due dates in separate columns horizontally. Their master sheet process works very well and I do not want to disrupt it by making any significant changes.
So the problem:
Essentially I need to be able to capture each 3 column stage and have it populate a new sheet. So every time a new row is added to the master sheet, the cells in columns ABC would be added to Sheet 1, the columns DEF would be added to sheet 2, GHI would be added to sheet 3, and so on. This would leave me with role/date based sheets appropriately formatted for gantt views and capacity management.
Tools/Resources:
I can do all of this outside or Smartsheet through various automations but would prefer to keep it within the native Smartsheet. I have a full license with access to Shuttle, Mesh, and Calendar tools.
Answers
-
Are you able to provide some screenshots for context?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Very fair question, here is a clean example of the format and what I am trying to do. Ideally the output would be a single combined sheet but I am happy to separate by role if needed:
-
How many work items are you expecting, and are you able to move work items off when they are completed?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Last year there were around 700 work items so depending on resources used it would be around 2,100 - 3,500 rows when split out be resources. They do track status at the work item level, so once all resource stages are complete the work item moves to a complete status.
On the output, the dates will be more meaningful than work item status so applying logic on status is something we can leverage but not a required aspect.
My current Working theory is to create a sheet for each role and try to apply a fairly complicated Lookup based with column formulas. Something along the lines of if work item # = blank, return no data, if not blank, reference work item # for lookup and return corresponding value. Then just build that formula in for each column in a sheet for each role. If there is a better solution I would love it though.
My alternative solution would be to do scheduled exports through data shuttle, use power query to structure data as needed, and then update a separate sheet using shuttle. Which works in theory but I don't like relying on so many moving parts that can break
-
At that scale you are going to want to break it out into separate sheets. In that case though, you could create a different report for each set of role columns that would be much more efficient on the back end than trying to create sheets with formulas.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks Paul, I appreciate the insight and time.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives