检查跨两列的冲突分配日期
长期读者，第一次海报——这让一些人难住了。
我有各种各样的事件与开始日期和结束日期。工作人员被分配为第一向导或第二向导(关键是我们要区分他们被分配的角色)。
我正在寻找一种方法，弹出一个警告标志时，其中一个名称被分配到冲突的事件。挑战在于，我们需要寻找“第一指南”中的冲突，“第二指南”中的冲突，以及两者之间的冲突。
这个公式似乎适用于检查单个列内的冲突:
=IF((ISBLANK([第一个指南]@row))， 0, IF(COUNTIFS([第一个指南]:[第一个指南]，@cell =[第一个指南]@row，[开始日期]:[开始日期]，@cell <=[结束日期]@row，[结束日期]:[结束日期]，@cell >=[开始日期]@row) > 1,1,0))
这是一种交叉检查两列的方法，我们无法找到解决方案，通过公式，辅助表，任何可行的方法。
非常感谢任何人谁可以破解这个!这将使很多人的生活更容易，这将允许我们使用日历应用程序直接安排。
最好的答案
-
杰夫·M。 ✭✭✭我想我已经解出来了!!!!!!
我创建了一个名为“第一面指引旗”的专栏，并将这个公式插入其中。
=IF((ISBLANK([1st Guide]@row))， 0, IF(COUNTIFS([1st Guide]:[1st Guide]， @cell = [1st Guide]@row，[开始日期]:[开始日期]，@cell <=[结束日期]@row，[结束日期]:[结束日期]，@cell >=[开始日期]@row) > 1, JOIN([1st Guide]@row)))
然后用这个公式命名为“第二导旗”的另一栏
=IF((ISBLANK([2nd Guide]@row))， 0, IF(COUNTIFS([2nd Guide]:[2nd Guide]， @cell = [2nd Guide]@row，[开始日期]:[开始日期]，@cell <=[结束日期]@row，[结束日期]:[结束日期]，@cell >=[开始日期]@row) > 1, JOIN([2nd Guide]@row)))
最后一栏是交叉检查" cross Check Flag"
=IF(COUNTIFS([第一个指南]:[第二个指南]，@cell =[第二个指南]@row，[开始日期]:A， @cell <=[结束日期]@row，[结束日期]:C， @cell >=[开始日期]@row) = 1, JOIN([第一个指南]@row:[第二个指南]@row))
这些列将返回冲突中的名称。
警告符号可以通过设置条件格式来实现，当相应的“Flag”列“非空白”时，将突出显示第一个指南或第二个指南单元格。
-
杰夫·M。 ✭✭✭
哦，是的，我忘了在我最初的回答中加上这一部分。使用这些字母的原因是，我创建了新的助手列，并将它们命名为A、B、c。我使用它们来排除故障并纠正与十字标志公式一起返回的错误消息。在使用COUNTIFS公式时，整个公式中的列范围必须相等。由于交叉检查公式同时评估第一指南和第二指南列，我添加了辅助列，将添加到每个日期列(例如[开始日期]:[辅助列A]和[结束日期]:[辅助列B])。
此外，您还需要确保“辅助列”直接位于另一个日期列旁边，以便列范围有效。添加了该列之后，您可以选择立即隐藏helper列。
答案
-
杰夫·M。 ✭✭✭
我想我已经解出来了!!!!!!
我创建了一个名为“第一面指引旗”的专栏，并将这个公式插入其中。
=IF((ISBLANK([1st Guide]@row))， 0, IF(COUNTIFS([1st Guide]:[1st Guide]， @cell = [1st Guide]@row，[开始日期]:[开始日期]，@cell <=[结束日期]@row，[结束日期]:[结束日期]，@cell >=[开始日期]@row) > 1, JOIN([1st Guide]@row)))
然后用这个公式命名为“第二导旗”的另一栏
=IF((ISBLANK([2nd Guide]@row))， 0, IF(COUNTIFS([2nd Guide]:[2nd Guide]， @cell = [2nd Guide]@row，[开始日期]:[开始日期]，@cell <=[结束日期]@row，[结束日期]:[结束日期]，@cell >=[开始日期]@row) > 1, JOIN([2nd Guide]@row)))
最后一栏是交叉检查" cross Check Flag"
=IF(COUNTIFS([第一个指南]:[第二个指南]，@cell =[第二个指南]@row，[开始日期]:A， @cell <=[结束日期]@row，[结束日期]:C， @cell >=[开始日期]@row) = 1, JOIN([第一个指南]@row:[第二个指南]@row))
这些列将返回冲突中的名称。
警告符号可以通过设置条件格式来实现，当相应的“Flag”列“非空白”时，将突出显示第一个指南或第二个指南单元格。
-
凯文·T ✭
我觉得你说对了!我只是不太明白“Cross Check Flag”公式中“A”和“C”指的是什么，我想我需要把我的头脑弄清楚，以便使它起作用。
…(开始日期):@cell < =(结束日期)@row[结束日期]:C…
但这比一个单一的怪物公式要优雅得多!
-
杰夫·M。 ✭✭✭
哦，是的，我忘了在我最初的回答中加上这一部分。使用这些字母的原因是，我创建了新的助手列，并将它们命名为A、B、c。我使用它们来排除故障并纠正与十字标志公式一起返回的错误消息。在使用COUNTIFS公式时，整个公式中的列范围必须相等。由于交叉检查公式同时评估第一指南和第二指南列，我添加了辅助列，将添加到每个日期列(例如[开始日期]:[辅助列A]和[结束日期]:[辅助列B])。
此外，您还需要确保“辅助列”直接位于另一个日期列旁边，以便列范围有效。添加了该列之后，您可以选择立即隐藏helper列。
帮助文章参考资料欧宝体育app官方888
类别
=IF([Weight (% of Completion]@row <> \"//www.santa-greenland.com/community/discussion/comment/319029/\", [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-13T22:24:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":59,"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-13T22:24:13+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-13T22:07:01+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":57,"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-13T22:07:01+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":"
