Please help with automation

N1k

I am creating a sheet that will be used to collect data from multiple departments. The data then needs to be rolled up to individual calendars to reflect stats by name and then the data from individual calendars will need to be rolled up into one collaborative calendar and reflect 'if this' data by colour.

I am stuck!

This project will be used nationally, within my organization, to reflect employee coverage at any given time. The first level calendar will show approved time off, reflected by names, to be viewed by leaders of a region. The next level calendar will show a collaboration of all regions but, rather than by name, it will need to reflect coverage by colour (20< away = red. 10 - 19 away = yellow. 10> = green.

Please provide any guidance you can to help me work out the automation and select the best calendar view.

Thank you in advance for your help!

Katy H

Smartsheet User 99 is correct, you would need to set up conditional formatting to allow the calendars to display different colors.

The "Task Bar" dropdown is where you would indicate the calendar coloring.

For example: If Contact Column = Katy H. then apply green task bar color to that entire row.

Task Bar coloring applies to gantt and calendar view. You are limited to 40 colors using this feature.

Katy H

@N1k I think maybe the plan you had in your head wasn't translated into the original question, but happy to try and help. Perhaps this process should be broken into pieces to help us understand what you are trying to accomplish.

Can you provide some more details about the process?

Below are some clarifying questions:

• I am assuming employees will be filling out a form for you to track employee coverage? If not, how will this information be collected?
• Do you need to create separate calendars? If yes, why, and will it be one calendar per location? Or one calendar per person?
• Maybe you can break down the steps you have imagined in your head?

I hope I can help! But it does sound like quite the project. I previously built something for a large corporation to count available seats on any given day for a variety of locations and it took A LOT of input, might be worth outsourcing if your company has the financial resources to back it. Smartsheet offers build services to support projects of this magnitude.

Katy H

Perhaps you can keep all data in one main sheet and then use a report filtering by "current user" to create a single location for all managers to see their own calendar? That or creating a report for each manager so you can have overview of each manager's information.

Smartsheet User 99

I would set up conditional format in this instance. Automation can't do color.

N1k

Thank you for the response. I actually need direction on finding the steps to roll the data up into individual calendars, by name, and then roll the individual calendars to a single one to show the conditional colours.

Katy H

Smartsheet User 99 is correct, you would need to set up conditional formatting to allow the calendars to display different colors.

The "Task Bar" dropdown is where you would indicate the calendar coloring.

For example: If Contact Column = Katy H. then apply green task bar color to that entire row.

Task Bar coloring applies to gantt and calendar view. You are limited to 40 colors using this feature.

N1k

This is helpful for the conditional formatting, for sure. And, thank you.

I am still struggling with the automation process when receiving forms from 200 people from 8 different locations to set up approval, consolidating the regional calendars, and then combining the 8 regions to one calendar that will reflect the conditional formatting. I am certain this project is way over my head but I'm not both committed and lost.

Katy H

@N1k I think maybe the plan you had in your head wasn't translated into the original question, but happy to try and help. Perhaps this process should be broken into pieces to help us understand what you are trying to accomplish.

Can you provide some more details about the process?

Below are some clarifying questions:

• I am assuming employees will be filling out a form for you to track employee coverage? If not, how will this information be collected?
• Do you need to create separate calendars? If yes, why, and will it be one calendar per location? Or one calendar per person?
• Maybe you can break down the steps you have imagined in your head?

I hope I can help! But it does sound like quite the project. I previously built something for a large corporation to count available seats on any given day for a variety of locations and it took A LOT of input, might be worth outsourcing if your company has the financial resources to back it. Smartsheet offers build services to support projects of this magnitude.

Andrée Starå

Hi @N1k

I hope you're well and safe!

To add to Katy H's excellent advice/answer.

How many calendars would you need?

One possible solution would be to collect it in a Master Sheet and then copy/move it to other separate sheets.

Make sense?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

N1k

@Andrée Starå

Putting my needs into words, with the limited knowledge I have of SS, was almost as tricky as trying to understand what the sheet needed.

I will try to break it down effectively, and thank you for asking for clarification:

1 department

8 regions

8 supervisors

Approx 200 employees

Using a form to submit time off requests

Each region will need a calendar providing visibility to the regional supervisors of time off

One master calendar will be required to show, at a glance, the combined data of people taking time off. This is to show the coverage in the office using colour indicators

Green will indicate adequate coverage and allow people to take time off

Yellow will indicate questionable coverage and may show a need to borrow employees from elsewhere

Red will indicate a critical shortage of employees and time off requests will be declined

I hope this makes more sense.

I've built the sheet with the required data, the form, and the data look up sheet. I'm trying to build the automation, triggered by any change on the sheet, that will send the request to the supervisors for approval, as well as to the timekeepers for review. I know I will have to capture the regions and created 8 separate automations of this type, which I have a bit of confidence in.

Where I have no experience is rolling this data into a calendar for each regional manager, and then to roll the details of the 8 calendars up to the national leadership to view with colour. I was given this task last week with the go live date being the 15th - of April.

I am desperate :-)

Katy H

Perhaps you can keep all data in one main sheet and then use a report filtering by "current user" to create a single location for all managers to see their own calendar? That or creating a report for each manager so you can have overview of each manager's information.

N1k

Creating 8 reports sounds like a very good option, Katy.

Is there a way that the report can be viewed as a calendar?

Katy H

https://community.smartsheet.com/discussion/comment/326381#Comment_326381

Yes! You can change the view using the dropdown on the upper left hand corner of the report. Just choose calendar view.

To view reports in calendar view you must have one or more date columns included in the report.