How do I add a sheet tame to calendar view when running a report?

Chris Shields

I'm working on a production schedule. I have multiple smart sheets for tracking each job's production dates, such as drafting, procurement , production, etc. The I run a report to create a calendar so that I can visually see where each job is sitting on the calendar so that I'm not over scheduling.

Problem is each individual smart sheet has the same task names, so when they show up on the calendar I'll just see something like multiple entries for "production". What i really need is to precede, or proceed (I don't care), each task name in the calendar with the sheet name, so that it looks more something like " PE0566 NKC Casework - production".

Any thoughts?

I'll attach a couple of pics of what I'm running and the output, so that you can see with having "production" twice on the calendar is a bity confusing, even though they come from separate sheets. I limited this to 2 projects for simplification of my example. In actuality I have about 6 that I need to plug in here at the moment, but the list will be growing.

Thank you!

Andrée Starå

Hi @Chris Shields

You'll need to add a so-called helper column with the sheet name and either join that with the Task name or add another column for the task name and then join the values in the Primary Column.

Make sense?

Would that work/help?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer! 

Chris Shields

Basically, I'm looking for the details from the Gantt to show up in the calendar for better visual representation.

Andrée Starå

Hi @Chris Shields

You'll need to add a so-called helper column with the sheet name and either join that with the Task name or add another column for the task name and then join the values in the Primary Column.

Make sense?

Would that work/help?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer! 

Chris Shields

What would this community be without your help, Andrée?

I think so. I don't know if I did exactly what you had in mind, or a variation of it.

Based on previous help that you gave me, I made helper columns in each individual project tracking Smartsheet (which I keep hidden). This let me track which task is a parent or grandparent, etc. This helped not only with the report, but helped me create conditionals to highlight sections of my sheets for easier readability. Then, and perhaps this is a cheat, I added the job name to each top parent process that I'm trying to collect in my production calendar (which isn't everything in my sheet. Just the main parts that relate to getting the things physically built).

The "CheckedParentHelperColumn" typically has a range of numbers from 1-3 depending on the tasks nested within, and sub=tasks nested within those. This helps with the conditional formatting. Such as adding task colors, which show up in the report.

In the report, I set the filter to look to see if the "CheckedParentHelperColumn" is set to "1" and then looked for rows that "contained" (not "equal to") words that related to the task that I'm looking for:

This yielded the desired results. I'm not done updating my sheets so you can see on this screen shot where I have because it attached the project name to the task's date range, and applied task color. I need to go back and add the information to the other, and future, sheets. Still have a little tweaking to do.

Sure, it's an extra step to add the project number and name to the parent tasks in each sheet, but what's I've found so far is that when I have multiple sheets opened, it helps with clarity of readability to know exactly which sheet I'm in. Sure, the name is on the top, but the redundancy is nice when I'm flipping back and worth between project workflow sheets. Especially when trying to balance the scheduling between multiple projects going on at once.

I appreciate your help and I am open to improvement suggestions, if you have any.

Thank you,

Chris

Andrée Starå

@Chris Shields

Thanks so much for the kind words!

You're more than welcome!

What I usually do in my client solutions is to add the fields to the Sheet Summary section or the top row and then reference them in helper columns to have them available on all rows.