Can I take cells from a horizontal row and add them to a new sheet as independent rows?

Aaron Gilbert · 01/24/24

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.

Paul Newcome · 01/24/24

Are you able to provide some screenshots for context?

Aaron Gilbert · 01/24/24

https://community.smartsheet.com/discussion/comment/415290#Comment_415290

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:

Aaron Gilbert · 01/24/24

https://community.smartsheet.com/discussion/116106/can-i-take-cells-from-a-horizontal-row-and-add-them-to-a-new-sheet-as-independent-rows

Paul Newcome · 01/24/24

How many work items are you expecting, and are you able to move work items off when they are completed?

Aaron Gilbert · 01/24/24

https://community.smartsheet.com/discussion/comment/415329#Comment_415329

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

Paul Newcome · 01/25/24

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.

Aaron Gilbert · 01/25/24

https://community.smartsheet.com/discussion/comment/415781#Comment_415781

Thanks Paul, I appreciate the insight and time.

Can I take cells from a horizontal row and add them to a new sheet as independent rows?

Answers

Categories