想直接在Smartsheet中练习使用公式吗?

请查看公式手册模板查看100多个公式，包括每个函数的词汇表以及常用和高级公式的示例。

公式与函数

被公式难住了?寻求帮助或寻找例子，看看其他人如何使用公式来解决类似的问题。

帮助文章资源欧宝体育app官方888

在Smartsheet中创建和编辑公式

交叉表参考的公式组合

Smartsheet功能列表

类别

受欢迎的标签

想直接在Smartsheet中练习使用公式吗?

请查看公式手册模板!

Give something like this a try...<\/p>

=IFERROR(INDEX(COLLECT({Value}, {Manager}, @cell = [Employee Manager]@row), COUNTIFS({Date}, @cell<= [This Sheet Date]@row, {Manager}, @cell = [Employee Manager]@row)), \"//www.santa-greenland.com/community/categories/formulas-and-functions/\")<\/p>

<\/p>

Since the reference sheet is being fed via copy row automation, we want to assume that all entries will be in chronological order from top to bottom with new entries going at the bottom of the sheet (so we don't have to actually interact with the reference sheet).<\/p>

<\/p>

So what we do is COLLECT all of the {Value} where the {Manager} is what we are looking for. We then INDEX this list of values, and counting how many dates (for that manager) are less than or equal to the sheet date should give us the entry number from the COLLECT function to pull.<\/p>

<\/p>

Example: <\/p>

We COLLECT all of the values where the manager is John Smith. This gives us a list with 3 entries on it.<\/p>

The date on the formula sheet is 06 December 2022. This means our COUNTIFS is going to output the number 2 which tells the INDEX function to pull the second entry from our list generated by the COLLECT function.<\/p>

<\/p>

I haven't had any coffee yet today. The explanation may not make much sense, but the formula should work for you. If you need clarification on anything, please don't hesitate, and I will try to explain a different way (after I have had some coffee).<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-12-30 15:09:14","updateUser":{"userID":139118,"name":"Kayla","url":"https:\/\/community.smartsheet.com\/profile\/Kayla","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!w4jS9-j6AyQ!-s9Rt9CEFHw!9QXda4h64Go","dateLastActive":"2022-12-30T15:05:51+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":285,"urlcode":"Solution Center","name":"Solution Center"}]},{"discussionID":99126,"type":"question","name":"Can we use JOIN with an IF Statement","excerpt":"Hi The scenario is I run a meeting with tasks for an agenda item and we have a list of them. Each task has a reference to the Agenda Item. eg. Agenda Item 1 - Action 1 - update plan Agenda Item 1 - Action 2 - distribute plan Agenda Item 2 - Action 1 - create resourcing requirements Agenda Item 2 - Action 2 - provide…","categoryID":322,"dateInserted":"2022-12-29T16:01:09+00:00","dateUpdated":null,"dateLastComment":"2022-12-29T16:54:45+00:00","insertUserID":89726,"insertUser":{"userID":89726,"name":"Purnima Gore","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Purnima%20Gore","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2022-12-29T16:53:08+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":67611,"lastUser":{"userID":67611,"name":"Michael Culley","url":"https:\/\/community.smartsheet.com\/profile\/Michael%20Culley","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/9BX019BZTFOW\/n0CCMUI3GCT23.jpg","dateLastActive":"2022-12-29T19:06:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":38,"score":null,"hot":3344666154,"url":"https:\/\/community.smartsheet.com\/discussion\/99126\/can-we-use-join-with-an-if-statement","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/99126\/can-we-use-join-with-an-if-statement","format":"Rich","lastPost":{"discussionID":99126,"commentID":355504,"name":"Re: Can we use JOIN with an IF Statement","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/355504#Comment_355504","dateInserted":"2022-12-29T16:54:45+00:00","insertUserID":67611,"insertUser":{"userID":67611,"name":"Michael Culley","url":"https:\/\/community.smartsheet.com\/profile\/Michael%20Culley","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/9BX019BZTFOW\/n0CCMUI3GCT23.jpg","dateLastActive":"2022-12-29T19:06:34+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":"2022-12-29T16:53:05+00:00","dateAnswered":"2022-12-29T16:52:47+00:00","acceptedAnswers":[{"commentID":355502,"body":"

Hi @Michael Culley<\/a> <\/p>

Just a slight adjustment and it worked.<\/p>

=JOIN(COLLECT([Action Item]:[Action Item], [Agenda Item]:[Agenda Item], \"1\"), \",\")<\/p>

Thanks for the suggestion above. got my brain thinking in another way :-)<\/p>

@toni.toni601<\/a> <\/p>

You have to right-click on a task's bar. You cannot do it on a parent row's bar.<\/p>

Yes:<\/p>

\n \n $\"image.png\"$ <\/img><\/a>\n <\/div>\n<\/div>\n

No:<\/p>

\n \n $\"image.png\"$ <\/img><\/a>\n <\/div>\n<\/div>\n

<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-12-28 21:37:14","updateUser":{"userID":155504,"name":"toni.toni601","url":"https:\/\/community.smartsheet.com\/profile\/toni.toni601","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-29T15:12:45+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":[]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式与函数

讨论表

帮助文章资源欧宝体育app官方888

类别

受欢迎的标签