连接两个单元格
大家好,
我刚开始使用smartsheet，看到他没有所有优秀的公式。我需要改变一个“状态”，当我改变一个列中的数字。
例如，当我将列“状态数据进入”更改为“批准”时，下一行将“状态数据进入”更改为“自由”，并且使用此公式可以正常工作:
IF([Status da enterga]5 = " avavada ";“LIBERADO”;IF([Status da enterga]5 = "EM REVISÃO";“AGUARDANDO”;IF([Status da enterga]5 = "REPROVADA";“BLOQUEADO”)))
我的问题是，我想添加一个新的标准来阅读“前任”专栏。列“前身”中具有相同数字的每一行也应更改“状态数据”。
有人知道怎么做吗?
谢谢!
维尼米盖尔
最佳答案
-
吉纳维芙P。 员工管理有一种方法可以做到这一点，但它有点复杂，需要一些额外的列。当我们引用上面的单元格时，您也无法将其创建为列公式，因为它看起来就像您在原始公式中所做的那样-而是向下拖动填充公式。
您需要增加的2列如下:
- 一个系统自动编号栏(我称我的栏为“自动”)
- 一个文字/数字列与列公式(我称之为“Row”)
Row列有一个公式，该公式将始终根据Auto number列返回当前行号，如果行被重新排列，该公式将更新:
=匹配((电子邮件保护);汽车:汽车;0）
现在你的公式可以用这个来求最小值根据row列和匹配的前身返回要返回的行。
我们从IF语句开始，它说，如果当前行有在它之前没有其他行使用相同的“Antecessor”，或者如果当前行有一个空白的“Antecessor”，则使用原始公式:
=如果(或(条件统计(祖先$ 1:(电子邮件保护);@cell =(电子邮件保护)) = 1;(电子邮件保护)= " ");IF([Status da enterga]5 = " avavada ";“LIBERADO”;IF([Status da enterga]5 = "EM REVISÃO";“AGUARDANDO”;IF([Status da enterga]5 = "REPROVADA";“BLOQUEADO”)));但是，如果这不是真的，这意味着在当前一行之上有一个具有相同前身的行，那么我们可以使用INDEX(-MIN(COLLECT())组合来指数返回“状态数据”列，并从该行返回值以上(-1)匹配的最小值行。
通俗地说:
- 查找工作表中与当前行具有相同祖先的最早的行
- 然后从行号减去1
- 并将该单词从其上一行的“状态数据”列中带回来
- 如果该单词与“this”单词匹配，则返回“this other”单词
IF(INDEX([Status data enterga]:[Status data enterga];MIN(收集(行:行;祖先:先行者;@cell =(电子邮件保护))) - 1) = "批准";“LIBERADO”;IF(INDEX([Status data enterga]:[Status data enterga];MIN(收集(行:行;祖先:先行者;@cell =(电子邮件保护)) - 1) = " em revisÃo ";“AGUARDANDO”;IF(INDEX([Status data enterga]:[Status data enterga];MIN(收集(行:行;祖先:先行者;@cell =(电子邮件保护))) - 1) = " reprovada ";“BLOQUEADO”))))完整的公式:
=如果(或(条件统计(祖先$ 1:(电子邮件保护);@cell =(电子邮件保护)) = 1;(电子邮件保护)= " ");IF([Status da enterga]5 = " avavada ";“LIBERADO”;IF([Status da enterga]5 = "EM REVISÃO";“AGUARDANDO”;IF([Status da enterga]5 = "REPROVADA";“BLOQUEADO”)));IF(INDEX([Status data enterga]:[Status data enterga];MIN(收集(行:行;祖先:先行者; @cell =(电子邮件保护))) - 1) = "批准";“LIBERADO”;IF(INDEX([Status data enterga]:[Status data enterga];MIN(收集(行:行;祖先:先行者;@cell =(电子邮件保护)) - 1) = " em revisÃo ";“AGUARDANDO”;IF(INDEX([Status data enterga]:[Status data enterga];MIN(收集(行:行;祖先:先行者;@cell =(电子邮件保护))) - 1) = " reprovada ";“BLOQUEADO”))))让我知道这是否适合你!
欢呼,
吉纳维芙
答案
-
维尼米盖尔 ✭你好各位,
有人知道这个问题的答案吗?
最好的问候,
维尼米盖尔
-
吉纳维芙P。 员工管理
公式如何知道这两行中哪一行具有“正确”的新状态?它总是在工作表上较高的行(第一行)还是有时较低的行具有新的状态?
-
维尼米盖尔 ✭
-
吉纳维芙P。 员工管理
有一种方法可以做到这一点，但它有点复杂，需要一些额外的列。当我们引用上面的单元格时，您也无法将其创建为列公式，因为它看起来就像您在原始公式中所做的那样-而是向下拖动填充公式。
您需要增加的2列如下:
- 一个系统自动编号栏(我称我的栏为“自动”)
- 一个文字/数字列与列公式(我称之为“Row”)
Row列有一个公式，该公式将始终根据Auto number列返回当前行号，如果行被重新排列，该公式将更新:
=匹配((电子邮件保护);汽车:汽车;0）
现在你的公式可以用这个来求最小值根据row列和匹配的前身返回要返回的行。
我们从IF语句开始，它说，如果当前行有在它之前没有其他行使用相同的“Antecessor”，或者如果当前行有一个空白的“Antecessor”，则使用原始公式:
=如果(或(条件统计(祖先$ 1:(电子邮件保护);@cell =(电子邮件保护)) = 1;(电子邮件保护)= " ");IF([Status da enterga]5 = " avavada ";“LIBERADO”;IF([Status da enterga]5 = "EM REVISÃO";“AGUARDANDO”;IF([Status da enterga]5 = "REPROVADA";“BLOQUEADO”)));但是，如果这不是真的，这意味着在当前一行之上有一个具有相同前身的行，那么我们可以使用INDEX(-MIN(COLLECT())组合来指数返回“状态数据”列，并从该行返回值以上(-1)匹配的最小值行。
通俗地说:
- 查找工作表中与当前行具有相同祖先的最早的行
- 然后从行号减去1
- 并将该单词从其上一行的“状态数据”列中带回来
- 如果该单词与“this”单词匹配，则返回“this other”单词
IF(INDEX([Status data enterga]:[Status data enterga];MIN(收集(行:行;祖先:先行者;@cell =(电子邮件保护))) - 1) = "批准";“LIBERADO”;IF(INDEX([Status data enterga]:[Status data enterga];MIN(收集(行:行;祖先:先行者;@cell =(电子邮件保护)) - 1) = " em revisÃo ";“AGUARDANDO”;IF(INDEX([Status data enterga]:[Status data enterga];MIN(收集(行:行;祖先:先行者;@cell =(电子邮件保护))) - 1) = " reprovada ";“BLOQUEADO”))))完整的公式:
=如果(或(条件统计(祖先$ 1:(电子邮件保护);@cell =(电子邮件保护)) = 1;(电子邮件保护)= " ");IF([Status da enterga]5 = " avavada ";“LIBERADO”;IF([Status da enterga]5 = "EM REVISÃO";“AGUARDANDO”;IF([Status da enterga]5 = "REPROVADA";“BLOQUEADO”)));IF(INDEX([Status data enterga]:[Status data enterga];MIN(收集(行:行;祖先:先行者; @cell =(电子邮件保护))) - 1) = "批准";“LIBERADO”;IF(INDEX([Status data enterga]:[Status data enterga];MIN(收集(行:行;祖先:先行者;@cell =(电子邮件保护)) - 1) = " em revisÃo ";“AGUARDANDO”;IF(INDEX([Status data enterga]:[Status data enterga];MIN(收集(行:行;祖先:先行者;@cell =(电子邮件保护))) - 1) = " reprovada ";“BLOQUEADO”))))让我知道这是否适合你!
欢呼,
吉纳维芙
帮助文章参考资料欧宝体育app官方888
类别
=IF([Weight (% of Completion]@row <> \"//www.santa-greenland.com/community/discussion/105730/\", [Weight (% of Completion]@row * [% Complete]@row)<\/p>
<\/p>
Then to add each piece together, you would use<\/p>
=SUM(CHILDREN([Helper Column]@row))<\/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":106318,"type":"question","name":"Look up multiple column values from Sheet B to match values in Sheet A to return value from Sheet B","excerpt":"Hello, I'm trying to make a formula that counts values from Sheet A against multiple columns in Sheet B, if only 1 total match is found, then lookup values from Sheet A against Sheet B and based on the result, pull in the value from a Status column. If either too many same results or no results are found return a text…","categoryID":322,"dateInserted":"2023-06-12T14:48:55+00:00","dateUpdated":null,"dateLastComment":"2023-06-13T11:45:16+00:00","insertUserID":158192,"insertUser":{"userID":158192,"name":"Clare123","url":"https:\/\/community.smartsheet.com\/profile\/Clare123","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-13T12:26:48+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"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-13T17:25:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":55,"score":null,"hot":3373242851,"url":"https:\/\/community.smartsheet.com\/discussion\/106318\/look-up-multiple-column-values-from-sheet-b-to-match-values-in-sheet-a-to-return-value-from-sheet-b","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106318\/look-up-multiple-column-values-from-sheet-b-to-match-values-in-sheet-a-to-return-value-from-sheet-b","format":"Rich","lastPost":{"discussionID":106318,"commentID":380268,"name":"Re: Look up multiple column values from Sheet B to match values in Sheet A to return value from Sheet B","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380268#Comment_380268","dateInserted":"2023-06-13T11:45:16+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-13T17:25:31+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-13T10:56:36+00:00","dateAnswered":"2023-06-13T09:33:21+00:00","acceptedAnswers":[{"commentID":380250,"body":"
Hi @Clare123<\/a> <\/p> I hope you're well and safe!<\/p> Yes, absolutely.<\/p> Here's the structure.<\/p> =INDEX({ColumnWithTheValueYouWantToShow}, MATCH(CellThatHaveTheValueToMatch@row,{ColumnWithTheValueToMatchAgainsTheCell}, 0))<\/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":[]},{"discussionID":106314,"type":"question","name":"Flexible Duration value when calculating scheduling dates","excerpt":"I have a Smartsheet which is a project plan with the Dependencies and Duration columns is being used in scheduling calculations to automatically deriving the start\/end dates for each task. However, I need the Duration to be dynamic e.g. linking cell to a cell in another sheet or to contain a formulae.. However, I cannot…","categoryID":322,"dateInserted":"2023-06-12T12:39:46+00:00","dateUpdated":null,"dateLastComment":"2023-06-13T11:22:45+00:00","insertUserID":162290,"insertUser":{"userID":162290,"name":"Mark Duddy","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Mark%20Duddy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-13T11:41:04+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":91566,"lastUser":{"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-13T16:02:16+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":56,"score":null,"hot":3373230151,"url":"https:\/\/community.smartsheet.com\/discussion\/106314\/flexible-duration-value-when-calculating-scheduling-dates","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106314\/flexible-duration-value-when-calculating-scheduling-dates","format":"Rich","lastPost":{"discussionID":106314,"commentID":380264,"name":"Re: Flexible Duration value when calculating scheduling dates","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380264#Comment_380264","dateInserted":"2023-06-13T11:22:45+00:00","insertUserID":91566,"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-13T16:02:16+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}},"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-13T11:24:19+00:00","dateAnswered":"2023-06-13T11:22:45+00:00","acceptedAnswers":[{"commentID":380264,"body":"
