Pull a calendar
Hi-- so, I have a series of columns representing various tasks. And then I have a series of rows representing various projects. I enter various dates into the corresponding cells (eg Project X and Task Y will occur on such and such date).
What I want to do then is on a separate sheet list all of the dates in the year and then have Smartsheet automatically pull the tasks/projects associated with a given date. So some dates would have nothing associated, and others might have several project/task pairs.
In a sense, this is a calendar view, which is fine, although I'd rather see it in list view.
Thoughts? Thank you!
Comments
-
J. Craig Williams ✭✭✭✭✭✭Since you have a list of all the dates, I'd suggest using
=ANOTHERFUNCTION(COLLECT({Task Column},NOT(ISBLANK())) ...
where ANOTHERFUNCTION could be a COUNTIFS or JOIN or something else.
Craig
-
JRose ✭
I think you've gone over my head a bit. May I pursue further?
So, if Task Columns = B through Z
and
Projects = 2 through 100
then--> under any given date listed on the separate sheet, the formula would be what? I'm not sure between COUNTIFS/JOIN, etc. And unless I'm misunderstanding your strategy would only pull from a single Task Column rather than all?
谢谢。
-
J. Craig Williams ✭✭✭✭✭✭
Apologies.
What I envisioned was
SHEET 1 -- a bunch of columns associated with tasks by row.
SHEET 2 -- a column of all the dates in a year, a column of only the task name AND/OR a column of the count of the tasks for that day.
If so, the solution in a nutshell.
You'll be using X-Sheet References on two columns -- the Task Name and the Task Date.
Next, you'll collect
https://help.smartsheet.com/function/collect
COLLECT({Task Name}, {Task Date}, [Calendar Date]@row)
where those are renamed for your setup.
Then JOIN them
https://help.smartsheet.com/function/join
JOIN(COLLECT(see above), " - ")
or COUNT them
https://help.smartsheet.com/function/count
COUNT(COLLECT(see above), " - ")
To add carriage returns to the JOIN, see this thread.
https://community.smartsheet.com/discussion/feature-request-join-carriage-return-separator
I hope that clears it up.
If you have a different problem, please clarify.
Craig
Help Article Resources
Categories
=IF(OR([Current Quarter]@row = \"Q1\", [Current Quarter]@row = \"Q2\"), \"S\" + RIGHT([Current Year]@row, 2), \"F\" + RIGHT([Current Year]@row, 2))<\/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"},{"tagID":279,"urlcode":"marketing","name":"Marketing"},{"tagID":281,"urlcode":"support","name":"Support"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}]},{"discussionID":111717,"type":"question","name":"Pull part of text from a cell","excerpt":"Hello community. I am trying to pull half of this text so that I just get the left hand side of \"╏\" from the Manufacturer | Model column The Manufacturer column is not working: =LEFT([Manufacturer | Model]@row, LEN([Manufacturer | Model]@row) - FIND(\"╏\", [Manufacturer | Model]@row)) Result = Systimax ╏ 360G2-1U- The Model…","snippet":"Hello community. I am trying to pull half of this text so that I just get the left hand side of \"╏\" from the Manufacturer | Model column The Manufacturer column is not working:…","categoryID":322,"dateInserted":"2023-10-16T17:19:54+00:00","dateUpdated":null,"dateLastComment":"2023-10-16T17:42:31+00:00","insertUserID":143463,"insertUser":{"userID":143463,"name":"Sam Swain","url":"https:\/\/community.smartsheet.com\/profile\/Sam%20Swain","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-16T18:32:00+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-10-17T03:35:19+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":24,"score":null,"hot":3394955545,"url":"https:\/\/community.smartsheet.com\/discussion\/111717\/pull-part-of-text-from-a-cell","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111717\/pull-part-of-text-from-a-cell","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":111717,"commentID":400182,"name":"Re: Pull part of text from a cell","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400182#Comment_400182","dateInserted":"2023-10-16T17:42:31+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-10-17T03:35:19+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/AA00F81A4FTU\/screenshot-2023-10-16-at-18-12-49.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/AA00F81A4FTU\/screenshot-2023-10-16-at-18-12-49.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/AA00F81A4FTU\/screenshot-2023-10-16-at-18-12-49.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/AA00F81A4FTU\/screenshot-2023-10-16-at-18-12-49.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/AA00F81A4FTU\/screenshot-2023-10-16-at-18-12-49.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/AA00F81A4FTU\/screenshot-2023-10-16-at-18-12-49.png"},"alt":"Screenshot 2023-10-16 at 18.12.49.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-16T18:15:44+00:00","dateAnswered":"2023-10-16T17:42:31+00:00","acceptedAnswers":[{"commentID":400182,"body":"
Try this one:<\/p>
=LEFT([Manufacturer | Model]@row, FIND(\"╏\", [Manufacturer | Model]@row) - 2)<\/p>
In the formula you posted, you are subtracting the location of your delimiter character (10) from the total length of the cell value (31). The result is 21, so it gives you the first 21 characters from the left. Instead, we just find the location of the delimiter and subtract two to account for the character itself and the space before it.<\/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"}]},{"discussionID":111704,"type":"question","name":"Custom Auto Numbering Function","excerpt":"Hi! New Smartsheet user here, appreciate any help and guidance. I'm looking to create a formula to build a custom auto-number function. I'm including a screenshot for reference. I'd like to automate the Project ID column. Where the project ID is YEAR - CLIENT - CLIENT PROJECT NUMBER. Is there a formula out there that would…","snippet":"Hi! New Smartsheet user here, appreciate any help and guidance. I'm looking to create a formula to build a custom auto-number function. I'm including a screenshot for reference.…","categoryID":322,"dateInserted":"2023-10-16T13:36:22+00:00","dateUpdated":null,"dateLastComment":"2023-10-16T19:46:06+00:00","insertUserID":168537,"insertUser":{"userID":168537,"name":"BrendonP","url":"https:\/\/community.smartsheet.com\/profile\/BrendonP","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/ACg8ocLStApCIdkhEJ3Z75kH4jR1mcljjo-xOMtR5CGeB5QEO5M=s96-c","dateLastActive":"2023-10-16T19:45:52+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":168537,"lastUser":{"userID":168537,"name":"BrendonP","url":"https:\/\/community.smartsheet.com\/profile\/BrendonP","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/ACg8ocLStApCIdkhEJ3Z75kH4jR1mcljjo-xOMtR5CGeB5QEO5M=s96-c","dateLastActive":"2023-10-16T19:45:52+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":23,"score":null,"hot":3394951348,"url":"https:\/\/community.smartsheet.com\/discussion\/111704\/custom-auto-numbering-function","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111704\/custom-auto-numbering-function","format":"Rich","tagIDs":[254,334],"lastPost":{"discussionID":111704,"commentID":400224,"name":"Re: Custom Auto Numbering Function","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400224#Comment_400224","dateInserted":"2023-10-16T19:46:06+00:00","insertUserID":168537,"insertUser":{"userID":168537,"name":"BrendonP","url":"https:\/\/community.smartsheet.com\/profile\/BrendonP","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/ACg8ocLStApCIdkhEJ3Z75kH4jR1mcljjo-xOMtR5CGeB5QEO5M=s96-c","dateLastActive":"2023-10-16T19:45:52+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/RTQWZ62I6QMN\/screenshot-2023-10-16-at-9-31-09-am.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTQWZ62I6QMN\/screenshot-2023-10-16-at-9-31-09-am.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTQWZ62I6QMN\/screenshot-2023-10-16-at-9-31-09-am.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTQWZ62I6QMN\/screenshot-2023-10-16-at-9-31-09-am.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTQWZ62I6QMN\/screenshot-2023-10-16-at-9-31-09-am.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTQWZ62I6QMN\/screenshot-2023-10-16-at-9-31-09-am.png"},"alt":"Screenshot 2023-10-16 at 9.31.09 AM.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-16T19:46:14+00:00","dateAnswered":"2023-10-16T17:44:23+00:00","acceptedAnswers":[{"commentID":400184,"body":"
You would just need to rearrange the sections.<\/p>
<\/p>
You can see in the formula there are two instances of<\/p>
+ \"-\" +<\/p>
<\/p>
Those are your delimiters. Everything to the left of the first one is what outputs the \"R\". Everything to the right of the second one outputs the \"20\", and everything in between outputs the count.<\/p>
<\/p>
So right now the formula is going<\/p>
left + \"-\" + middle + \"-\" + right<\/p>
<\/p>
You want<\/p>
right + \"-\" + left + \"-\" + middle<\/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"},{"tagID":334,"urlcode":"automations","name":"Automations"}]}],"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":[]}">
