Creating training Calendar
Hi All,
I am currently trying to create a sheet to track who our trainers are currently working with. Currently have 15 trainers, but the team will grow larger in the next few months. Each trainer can train 2 employees at a given time. Employees are trained for 2 weeks.
Ideally, I would like a form that someone can request training through, the training can be approved/denied by the training coordinator, training coordinator looks at the calendar to chose the start and finish date, and then that populates into a calendar. The calendar should send some sort of error when the coordinator tries to book a trainer for more than 2 employees at a time. Calendar should also be visually appealing for upper mgmt.
Any suggestions? I've made a form, and a rough draft of the calendar, but it's not meeting all the needs.
Thanks!
Answers
-
Antonio Figueroa ✭✭✭✭✭✭I would use Resource Management on your sheet using Gantt View. Remember to turn on dependency first.
-
Genevieve P. Employee AdminHi@NP2_
It sounds like you've already set up the first half:
- A form submits a new row
- This new row triggers an alert to the Training Coordinator (or an Update Request if you want them to fill out the details right from their email)
- The Training Coordinator can then update the same row with the Start/End/Trainer
Then what I would do here is have a Flag Symbol column that uses a formula to see if the same Trainer has more than 2 rows on those same dates.
=IF(COUNTIFS(Trainer:Trainer,[email protected], [Start Date]:[Start Date], <=[End Date]@row, [End Date]:[End Date], >=[Start Date]@row) > 2, 1, 0)
This way you can easily identify if a row need to be updated, and adjust the most recent entry. You can also set up another Alert to the Training Coordinator if any rows suddenly appear with a flag and Conditional Formatting to highlight the rows.
Then for your higher-level overview:
I woulduse Conditional Formattingso each Trainer has a specific colour. This is another way to visualize if there are more than 2 of the same colour on one day.
Then you can create a ReportandGroup itby the Trainer. You can put this in Gantt view to see what's scheduled, and set it toSort by Dateso that everything is in order in the Gantt view, like so:
This way your Training Coordinator can go to the Report and adjust the overlapping dates for the flagged rows, seeing what is available while adjusting the data in the grid view on the left.
我ope that helps!
Cheers,
Genevieve
-
NP2_ ✭✭
Hi Genevieve! I appreciate the help!
I am having a few issues though... I edited the formula to match my column names but can't seem to get it to work. I also created the conditional formatting but it won't apply to the gnatt chart for some reason. Any suggestions?
Thanks again!
-
Genevieve P. Employee Admin
Hi@NP2_
When you say it's not working, are you getting an error? Can you copy/paste your formula here?
For theConditional Formatting (see here), the Gantt Chart colour is theTask Barcolour in the formatting option. You have to have the criteria set to apply to the Entire Row for the Task Bar to appear.
Let me know if that helped!
-
NP2_ ✭✭
Here is my formula:
=IF(COUNTIFS(Assigned trainer:Assigned trainer, Assigned[email protected], [Start]:[Start], <=[Finish]@row, [Finish]:[Finish], >=[Start]@row) > 2, 1, 0)
I used yours as my starting point, and changed the names of the rows in the formula to match my sheet. When I use it, it says #unparseable
Thank you!
-
Genevieve P. Employee Admin
Hi@NP2_
If a column name has a space or a number in it, it needs to be referenced [in these] so the formula knows when the name starts and stops. However if there arenospaces or numbers you can list the names outright without [these].
Try this:
=IF(COUNTIFS([Assigned trainer]:[Assigned trainer], [Assigned trainer]@row, Start:Start, <=[email protected], Finish:Finish, >[email protected]) > 2, 1, 0)
See:创建一个细胞或列引用公式
Cheers!
Genevieve
-
NP2_ ✭✭
Genevieve,
That worked! Thank you so much!
-
Genevieve P. Employee Admin
No problem at all! Thanks for letting me know it worked
-
NP2_ ✭✭
Another question for you.... Is there a way that I can make the row be flagged if a trainer has not been assigned, or the trainer assigned is overbooked (which has been changed to be more than one trainee at a time)?
Here is my current formula:
=IF(COUNTIFS([Assigned trainer]:[Assigned trainer], [Assigned trainer]@row, [Start Date]:[Start Date], <=[Tentative Finish Date]@row, [Tentative Finish Date]:[Tentative Finish Date], >=[Start Date]@row) > 0, 1)
-
Genevieve P. Employee Admin
Hi@NP2_
It sounds like you just want to add a rule that if the cell is blank, flag the flag as well?
Try this:
=IF(OR([Assigned trainer]@row = "",COUNTIFS([Assigned trainer]:[Assigned trainer], [Assigned trainer]@row, [Start Date]:[Start Date], <=[Tentative Finish Date]@row, [Tentative Finish Date]:[Tentative Finish Date], >=[Start Date]@row) > 0),1)
Cheers,
Genevieve
-
NP2_ ✭✭
Thank you so much! That worked perfectly!
Help Article Resources
Categories
@SarahI<\/a> Yes you can do that just use CHILDREN([COLUMN NAME])<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106901,"type":"question","name":"Can you use a COUNTIF formula to county symbols?","excerpt":"I have a list of goals for the year and we are using harvey ball symbols to indicate the percent complete an the red, yellow, green, gray balls to indicate the health\/status of the project. I would like to display a report on my dashboard that shows the number of projects we have in each harvey ball stage and also in each…","categoryID":322,"dateInserted":"2023-06-26T15:59:48+00:00","dateUpdated":"2023-06-26T16:00:12+00:00","dateLastComment":"2023-06-26T16:29:45+00:00","insertUserID":162764,"insertUser":{"userID":162764,"name":"Stephanie D","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Stephanie%20D","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mctWfgWrL7o!n-DX3ymVfmQ!msa32mT4_k9","dateLastActive":"2023-06-26T16:52:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":162764,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-26T23:23:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3375593973,"url":"https:\/\/community.smartsheet.com\/discussion\/106901\/can-you-use-a-countif-formula-to-county-symbols","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106901\/can-you-use-a-countif-formula-to-county-symbols","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106901,"commentID":382346,"name":"Re: Can you use a COUNTIF formula to county symbols?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382346#Comment_382346","dateInserted":"2023-06-26T16:29:45+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-26T23:23:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-26T16:24:48+00:00","dateAnswered":"2023-06-26T16:08:42+00:00","acceptedAnswers":[{"commentID":382328,"body":" Hi @Stephanie D<\/a> <\/p> I hope you're well and safe!<\/p> Try something like this.<\/p> =COUNTIF(Status:Status, \"Green\")<\/p> =COUNTIF([% Complete]:[% Complete], \"Half\")<\/p> Did that work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
![https:\/\/us.v-cdn.net\/6031209\/uploads\/SRFE2SA2TK70\/screen-shot-2021-11-19-at-11-53-52-am.png<\/a><\/p>This way you can easily identify if a row need to be updated, and adjust the most recent entry. You can also set up another Alert to the Training Coordinator if any rows suddenly appear with a flag and Conditional Formatting to highlight the rows.<\/p> <\/p>Then for your higher-level overview:<\/p>I would use Conditional Formatting<\/a> so each Trainer has a specific colour. This is another way to visualize if there are more than 2 of the same colour on one day. <\/p>https:\/\/us.v-cdn.net\/6031209\/uploads\/AULWGEEI5CAN\/screen-shot-2021-11-19-at-11-55-57-am.png<\/a><\/p>Then you can create a Report and Group it <\/strong><\/a>by the Trainer. You can put this in Gantt view to see what's scheduled, and set it to Sort by Date <\/a>so that everything is in order in the Gantt view, like so:<\/p>https:\/\/us.v-cdn.net\/6031209\/uploads\/MF6DX1TRFNK7\/screen-shot-2021-11-19-at-11-45-08-am.png<\/a><\/p>This way your Training Coordinator can go to the Report and adjust the overlapping dates for the flagged rows, seeing what is available while adjusting the data in the grid view on the left. <\/p>I hope that helps!<\/p>Cheers,<\/p>Genevieve<\/p>","bodyRaw":"[{\"insert\":\"Hi \"},{\"insert\":{\"mention\":{\"name\":\"NP2_\",\"userID\":141822}}},{\"insert\":\" \\nIt sounds like you've already set up the first half:\\nA form submits a new row\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"This new row triggers an alert to the Training Coordinator (or an Update Request if you want them to fill out the details right from their email)\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"The Training Coordinator can then update the same row with the Start\\\/End\\\/Trainer \"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"\\nThen what I would do here is have a Flag Symbol column that uses a formula to see if the same Trainer has more than 2 rows on those same dates. \\n=IF(COUNTIFS(Trainer:Trainer, Trainer@row, [Start Date]:[Start Date], <=[End Date]@row, [End Date]:[End Date], >=[Start Date]@row) > 2, 1, 0)\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/SRFE2SA2TK70\\\/screen-shot-2021-11-19-at-11-53-52-am.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/SRFE2SA2TK70\\\/screen-shot-2021-11-19-at-11-53-52-am.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"This way you can easily identify if a row need to be updated, and adjust the most recent entry. You can also set up another Alert to the Training Coordinator if any rows suddenly appear with a flag and Conditional Formatting to highlight the rows.\\n \\nThen for your higher-level overview:\\nI would \"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/learning-track\\\/level-1-get-started\\\/conditional-formatting\"},\"insert\":\"use Conditional Formatting\"},{\"insert\":\" so each Trainer has a specific colour. This is another way to visualize if there are more than 2 of the same colour on one day. \\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/AULWGEEI5CAN\\\/screen-shot-2021-11-19-at-11-55-57-am.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/AULWGEEI5CAN\\\/screen-shot-2021-11-19-at-11-55-57-am.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\nThen you can create a Report \"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2482082-configure-grouping-to-organize-results-in-report-builder\"},\"insert\":\"and \"},{\"attributes\":{\"bold\":true,\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2482082-configure-grouping-to-organize-results-in-report-builder\"},\"insert\":\"Group it \"},{\"insert\":\"by the Trainer. You can put this in Gantt view to see what's scheduled, and set it to \"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2482084-sort-your-report-results-with-report-builder\"},\"insert\":\"Sort by Date \"},{\"insert\":\"so that everything is in order in the Gantt view, like so:\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/MF6DX1TRFNK7\\\/screen-shot-2021-11-19-at-11-45-08-am.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/MF6DX1TRFNK7\\\/screen-shot-2021-11-19-at-11-45-08-am.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"This way your Training Coordinator can go to the Report and adjust the overlapping dates for the flagged rows, seeing what is available while adjusting the data in the grid view on the left. \\nI hope that helps!\\nCheers,\\nGenevieve\\n\"}]","format":"rich","dateInserted":"2021-11-19T11:59:14+00:00","insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-26T16:24:30+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/311443#Comment_311443","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/311443#Comment_311443](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/SRFE2SA2TK70\/screen-shot-2021-11-19-at-11-53-52-am.png\"){kind=link}
![https:\/\/us.v-cdn.net\/6031209\/uploads\/AULWGEEI5CAN\/screen-shot-2021-11-19-at-11-55-57-am.png<\/a><\/p>Then you can create a Report and Group it <\/strong><\/a>by the Trainer. You can put this in Gantt view to see what's scheduled, and set it to Sort by Date <\/a>so that everything is in order in the Gantt view, like so:<\/p>https:\/\/us.v-cdn.net\/6031209\/uploads\/MF6DX1TRFNK7\/screen-shot-2021-11-19-at-11-45-08-am.png<\/a><\/p>This way your Training Coordinator can go to the Report and adjust the overlapping dates for the flagged rows, seeing what is available while adjusting the data in the grid view on the left. <\/p>I hope that helps!<\/p>Cheers,<\/p>Genevieve<\/p>","bodyRaw":"[{\"insert\":\"Hi \"},{\"insert\":{\"mention\":{\"name\":\"NP2_\",\"userID\":141822}}},{\"insert\":\" \\nIt sounds like you've already set up the first half:\\nA form submits a new row\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"This new row triggers an alert to the Training Coordinator (or an Update Request if you want them to fill out the details right from their email)\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"The Training Coordinator can then update the same row with the Start\\\/End\\\/Trainer \"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"\\nThen what I would do here is have a Flag Symbol column that uses a formula to see if the same Trainer has more than 2 rows on those same dates. \\n=IF(COUNTIFS(Trainer:Trainer, Trainer@row, [Start Date]:[Start Date], <=[End Date]@row, [End Date]:[End Date], >=[Start Date]@row) > 2, 1, 0)\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/SRFE2SA2TK70\\\/screen-shot-2021-11-19-at-11-53-52-am.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/SRFE2SA2TK70\\\/screen-shot-2021-11-19-at-11-53-52-am.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"This way you can easily identify if a row need to be updated, and adjust the most recent entry. You can also set up another Alert to the Training Coordinator if any rows suddenly appear with a flag and Conditional Formatting to highlight the rows.\\n \\nThen for your higher-level overview:\\nI would \"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/learning-track\\\/level-1-get-started\\\/conditional-formatting\"},\"insert\":\"use Conditional Formatting\"},{\"insert\":\" so each Trainer has a specific colour. This is another way to visualize if there are more than 2 of the same colour on one day. \\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/AULWGEEI5CAN\\\/screen-shot-2021-11-19-at-11-55-57-am.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/AULWGEEI5CAN\\\/screen-shot-2021-11-19-at-11-55-57-am.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\nThen you can create a Report \"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2482082-configure-grouping-to-organize-results-in-report-builder\"},\"insert\":\"and \"},{\"attributes\":{\"bold\":true,\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2482082-configure-grouping-to-organize-results-in-report-builder\"},\"insert\":\"Group it \"},{\"insert\":\"by the Trainer. You can put this in Gantt view to see what's scheduled, and set it to \"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2482084-sort-your-report-results-with-report-builder\"},\"insert\":\"Sort by Date \"},{\"insert\":\"so that everything is in order in the Gantt view, like so:\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/MF6DX1TRFNK7\\\/screen-shot-2021-11-19-at-11-45-08-am.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/MF6DX1TRFNK7\\\/screen-shot-2021-11-19-at-11-45-08-am.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"This way your Training Coordinator can go to the Report and adjust the overlapping dates for the flagged rows, seeing what is available while adjusting the data in the grid view on the left. \\nI hope that helps!\\nCheers,\\nGenevieve\\n\"}]","format":"rich","dateInserted":"2021-11-19T11:59:14+00:00","insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-26T16:24:30+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/311443#Comment_311443","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/311443#Comment_311443](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/AULWGEEI5CAN\/screen-shot-2021-11-19-at-11-55-57-am.png\"){kind=link}
![https:\/\/us.v-cdn.net\/6031209\/uploads\/MF6DX1TRFNK7\/screen-shot-2021-11-19-at-11-45-08-am.png<\/a><\/p>This way your Training Coordinator can go to the Report and adjust the overlapping dates for the flagged rows, seeing what is available while adjusting the data in the grid view on the left. <\/p>I hope that helps!<\/p>Cheers,<\/p>Genevieve<\/p>","bodyRaw":"[{\"insert\":\"Hi \"},{\"insert\":{\"mention\":{\"name\":\"NP2_\",\"userID\":141822}}},{\"insert\":\" \\nIt sounds like you've already set up the first half:\\nA form submits a new row\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"This new row triggers an alert to the Training Coordinator (or an Update Request if you want them to fill out the details right from their email)\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"The Training Coordinator can then update the same row with the Start\\\/End\\\/Trainer \"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"\\nThen what I would do here is have a Flag Symbol column that uses a formula to see if the same Trainer has more than 2 rows on those same dates. \\n=IF(COUNTIFS(Trainer:Trainer, Trainer@row, [Start Date]:[Start Date], <=[End Date]@row, [End Date]:[End Date], >=[Start Date]@row) > 2, 1, 0)\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/SRFE2SA2TK70\\\/screen-shot-2021-11-19-at-11-53-52-am.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/SRFE2SA2TK70\\\/screen-shot-2021-11-19-at-11-53-52-am.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"This way you can easily identify if a row need to be updated, and adjust the most recent entry. You can also set up another Alert to the Training Coordinator if any rows suddenly appear with a flag and Conditional Formatting to highlight the rows.\\n \\nThen for your higher-level overview:\\nI would \"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/learning-track\\\/level-1-get-started\\\/conditional-formatting\"},\"insert\":\"use Conditional Formatting\"},{\"insert\":\" so each Trainer has a specific colour. This is another way to visualize if there are more than 2 of the same colour on one day. \\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/AULWGEEI5CAN\\\/screen-shot-2021-11-19-at-11-55-57-am.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/AULWGEEI5CAN\\\/screen-shot-2021-11-19-at-11-55-57-am.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\nThen you can create a Report \"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2482082-configure-grouping-to-organize-results-in-report-builder\"},\"insert\":\"and \"},{\"attributes\":{\"bold\":true,\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2482082-configure-grouping-to-organize-results-in-report-builder\"},\"insert\":\"Group it \"},{\"insert\":\"by the Trainer. You can put this in Gantt view to see what's scheduled, and set it to \"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2482084-sort-your-report-results-with-report-builder\"},\"insert\":\"Sort by Date \"},{\"insert\":\"so that everything is in order in the Gantt view, like so:\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/MF6DX1TRFNK7\\\/screen-shot-2021-11-19-at-11-45-08-am.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/MF6DX1TRFNK7\\\/screen-shot-2021-11-19-at-11-45-08-am.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"This way your Training Coordinator can go to the Report and adjust the overlapping dates for the flagged rows, seeing what is available while adjusting the data in the grid view on the left. \\nI hope that helps!\\nCheers,\\nGenevieve\\n\"}]","format":"rich","dateInserted":"2021-11-19T11:59:14+00:00","insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-26T16:24:30+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/311443#Comment_311443","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/311443#Comment_311443](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/MF6DX1TRFNK7\/screen-shot-2021-11-19-at-11-45-08-am.png\"){kind=link}