How to take data from a form entry and modify to create separate rows

Rebekah O

I am currently working on a project to shift the view of a time entry form so we can more easily complete analysis. Right now, the data is entered via a form and appears on one row in the master sheet. I need to take that data and shift it from everything being on one row to multiple rows. What I am looking at is basically this:

and needing it to be this:

Each time sheet is currently one week for one team member for one project. I need it at the one task (with its details), one team member, one project level.

HELP!

Eric Law

@Rebekah O You're going to need a few helper sheets, an intake sheet, and a final sheet.

Intake sheet will send information to the helper sheets.

Helper sheet will rename columns for you and shed excess information

After helper sheets do their magic, they will send it to the final sheet.

EDIT: Please reach out if you need help with the process.

Rebekah O

https://community.smartsheet.com/discussion/comment/394353#Comment_394353

I'm not sure how I would use helper sheets in this way. Can you provide me with more details?

Eric Law

@Rebekah O

Yes, you would create the amount of sheets that you have Tasks. So name them Task A, Task B, etc

You will create an automation from the intake sheet to Copy rows to all the different sheets when a row gets added. I would create an automation on the intake sheet to move the rows at the end of the day to an archive intake at the end of every day or week.

In your helper sheets, you will make sure they have Date, Task, Hour, Description without subfixes of numbers/letters. You will create a column formula for those columns and link them to the proper cell. Then have the row move to the final sheet.

In lieu of a final sheet, you can also create a report that pulls in from the helper sheets to only show the columns you want (since they will all be named the same thing)

Maybe someone else has a more elegant way to do this, if so, I would also like to know. 😅