从另一张表返回列表

Matt_w_questions
编辑12/09/19 公式和函数

任何人都可以提供一些帮助下面的公式-试图返回一个列表的信息相对于员工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),""))

评论

帮助文章参考资料欧宝体育app官方888

想要直接在智能表中练习使用公式吗?

请查看公式手册模板!
30, {011-AP Archive -# of Days to Complete}, <60) I was able to do this same formula with a COUNTIFS but its' not working the same, what am I missing.","categoryID":322,"dateInserted":"2023-06-16T15:00:58+00:00","dateUpdated":null,"dateLastComment":"2023-06-16T15:22:46+00:00","insertUserID":156010,"insertUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-16T15:22:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":156010,"lastUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-16T15:22:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":19,"score":null,"hot":3373857824,"url":"https:\/\/community.smartsheet.com\/discussion\/106550\/invalid-operation","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106550\/invalid-operation","format":"Rich","lastPost":{"discussionID":106550,"commentID":381027,"name":"Re: Invalid Operation","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381027#Comment_381027","dateInserted":"2023-06-16T15:22:46+00:00","insertUserID":156010,"insertUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-16T15:22:32+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-16T15:22:31+00:00","dateAnswered":"2023-06-16T15:19:44+00:00","acceptedAnswers":[{"commentID":381026,"body":"

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":[]}">

公式和函数趋势