从另一张表返回列表
任何人都可以提供一些帮助下面的公式-试图返回一个列表的信息相对于员工ID号的列。每个ID将有几个匹配,例如Matt工作项目1..2…3.和7。我想返回那些条目的ID相关信息。
提前感谢!
=IFERROR(INDEX({Phone TESI Data Tracker - 1 Assigned Items Range 1},SMALL(IF([工作列-不修改]1={Phone TESI Data Tracker - 1 Assigned Items Range 1},ROW{Phone TESI Data Tracker - 1 Assigned Items Range 1}({Phone TESI Data Tracker - 1 Assigned Items Range 1})-2,""), ROW{Phone TESI Data Tracker - 1 Assigned Items Range 2},2),""))
评论
-
Shaine格林伍德 员工
嗨,马特,
似乎您正在尝试使用ROW函数,该函数在Smartsheet中不存在。您可以在这里查看我们的完整功能列表:https://help.smartsheet.com/functions
你也可能对@cell和@row参数:https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell
您还可以考虑使用INDEX和MATCH函数创建一个公式,以补充您想要对ROW执行的操作。这里有一个社区帖子,介绍了它们的实施情况:https://community.smartsheet.com/discussion/how-index-match-multiple-criteria
帮助文章参考资料欧宝体育app官方888
类别
Try this:<\/p>
SUMIFS({011-AP Archive -# of Days to Complete}, {011-AP Archive -# of Days to Complete}, > 30, {011-AP Archive -# of Days to Complete}, < 60)<\/p>
With SUMIFS, you have to include the range to SUM separately from the ranges to compare. In your scenario, they are the same, but can be different.<\/p>
SUMIFS( range, criterion_range1, criterion1, [ criterion_range2, criterion2... ])<\/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":106512,"type":"question","name":"Mandatory \"pick value from list\" field in Global Update","excerpt":"Issue with the Global Update feature, specifically when trying to add a new value to an existing column without altering any existing values. Here's the situation: Objective: Add the value \"Cancelled\" to the \"Status\" column in the Project Plan. Approach: Global Update functionality to ensure this change is reflected across…","categoryID":322,"dateInserted":"2023-06-15T17:51:44+00:00","dateUpdated":"2023-06-15T18:13:35+00:00","dateLastComment":"2023-06-16T11:23:17+00:00","insertUserID":151950,"insertUser":{"userID":151950,"name":"Mirelle Almeida","url":"https:\/\/community.smartsheet.com\/profile\/Mirelle%20Almeida","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hTjmoZuUKqs!clkvh_ZG87s!PpEIw21w9kv","dateLastActive":"2023-06-16T11:19:41+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":151950,"lastUserID":151950,"lastUser":{"userID":151950,"name":"Mirelle Almeida","url":"https:\/\/community.smartsheet.com\/profile\/Mirelle%20Almeida","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hTjmoZuUKqs!clkvh_ZG87s!PpEIw21w9kv","dateLastActive":"2023-06-16T11:19:41+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":30,"score":null,"hot":3373767301,"url":"https:\/\/community.smartsheet.com\/discussion\/106512\/mandatory-pick-value-from-list-field-in-global-update","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106512\/mandatory-pick-value-from-list-field-in-global-update","format":"Rich","lastPost":{"discussionID":106512,"commentID":380989,"name":"Re: Mandatory \"pick value from list\" field in Global Update","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380989#Comment_380989","dateInserted":"2023-06-16T11:23:17+00:00","insertUserID":151950,"insertUser":{"userID":151950,"name":"Mirelle Almeida","url":"https:\/\/community.smartsheet.com\/profile\/Mirelle%20Almeida","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hTjmoZuUKqs!clkvh_ZG87s!PpEIw21w9kv","dateLastActive":"2023-06-16T11:19:41+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-16T11:23:58+00:00","dateAnswered":"2023-06-15T23:55:36+00:00","acceptedAnswers":[{"commentID":380951,"body":"
Hi Mirelle,<\/p>
<\/p>
When applying the global update to modify the column, right under the selection of the column name, there are two checkboxes. One to change column property and the other is to change column cell data. Both are checked by default. To ensure only the new dropdown values are pushed and no existing values are updated, all you have to do it uncheck the box which says Change column cell data. This will remove the Pick value from list option altogether and you can apply the update.<\/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":106532,"type":"question","name":"How to Delimit Cells in Formulas","excerpt":"Hi, I have two columns: I want the second column to be delimited (trim) at just the release version. Unfortunately, I'm not super experienced with formulas. While I was able to get this formula to work for 1 row, it's easy to see how this isn't scalable. I looked for instructions on trimming and delimiting but all the…","categoryID":322,"dateInserted":"2023-06-15T21:40:25+00:00","dateUpdated":null,"dateLastComment":"2023-06-15T23:09:21+00:00","insertUserID":160235,"insertUser":{"userID":160235,"name":"SergeantPup","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/SergeantPup","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-16T14:24:10+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162395,"lastUser":{"userID":162395,"name":"AravindGP","title":"Principal Consultant at atturra","url":"https:\/\/community.smartsheet.com\/profile\/AravindGP","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!gj8p3_p0u-o!2qNpfwZ83HI!-yrLbvX61mQ","dateLastActive":"2023-06-16T04:22:11+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":23,"score":null,"hot":3373736386,"url":"https:\/\/community.smartsheet.com\/discussion\/106532\/how-to-delimit-cells-in-formulas","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106532\/how-to-delimit-cells-in-formulas","format":"Rich","lastPost":{"discussionID":106532,"commentID":380949,"name":"Re: How to Delimit Cells in Formulas","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380949#Comment_380949","dateInserted":"2023-06-15T23:09:21+00:00","insertUserID":162395,"insertUser":{"userID":162395,"name":"AravindGP","title":"Principal Consultant at atturra","url":"https:\/\/community.smartsheet.com\/profile\/AravindGP","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!gj8p3_p0u-o!2qNpfwZ83HI!-yrLbvX61mQ","dateLastActive":"2023-06-16T04:22:11+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/RY7DT9DALZCH\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-16T01:16:00+00:00","dateAnswered":"2023-06-15T23:09:21+00:00","acceptedAnswers":[{"commentID":380949,"body":"
Hi There,<\/p>
<\/p>
You can have a formula in AFO Version and make it a column formula. The formula would be \"=Right([Primary Column]@row, 4)\". This formula will pick up the last 4 characters of the text in your primary column.<\/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":[]}],"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":[]}">