Automate label/ name addition in primary column

kayoba84

Hi, am new to Smartsheet. Right now I'm using Smartsheet to monitor a project on a weekly basis. I have 3 levels of label in the primary column - 1st : Project Title, 2nd Week label (Week 1, 2 ....). 3rd level is the actual activity with start, end dates, with dependencies. The date range within a week marker is based on a 5 day workday - Monday to Friday.

Is there any way to automatically create a new Week label and copy an activity which end date overflows into the week after? Say an activity starts on Week 1 Monday and to be completed by Thursday same week, but delayed to Tuesday in the next week...I'm looking for a way to automate the creation of a line item of said activity starting from Week 2 Monday and ending on Week 2 Tuesday assuming there's an existing Week 2 label, and to add "Week 2" label if there none.

Hope I am not being too confusing. Thank you for any and all pointers and guidance

Anson Cheung

I would not design the hierarchy of project tasks like that. If you just want this presentation, you may add a formula column to derive the "Week No" by start/end dates and create a sheet report grouping the tasks by the "Week No" column.

kayoba84

Hi Anson, thanks for the response. Sounds like a good solution, but being a newbie, I wouldnt know where to start/ go next with your suggestion

Austin Smith

Put your start date in a summary field (little sheet icon with a tab on it to the right/center of your sheet) for ease in changing data in bulk - keeps you from having to edit the formula constantly

Formula column (called [Weeks] in the example below) =ROUNDDOWN(IF(Start@row = [Start Date]#, 1, IF(Start@row > [Start Date]#, (Start@row - [Start Date]#) / 7, "n/a")), 0)

Start@row is the start date field in your sheet

[Start Date]# is the summary data (seen on the right)

Once you've built this out, you can just build a report that shows you a breakdown for a single week, series of weeks, or break the job up into weeks.