我需要使用IF / THEN公式来比较单元格从一列到另一列中的单元格

Geoff时
Geoff时 ✭
在公式和函数

我真的需要一些帮助!提前感谢!

我的状态栏有以下每个单元格可用的颜色选项:红色、黄色、绿色和蓝色。

我希望使用以下IF / THEN语句:

1.在状态，如果交货日期在开始日期后的七(7)天内，则交货日期为红色的．DELIVERY DATE单元格的颜色也可以更改为红色吗?

2.在状态，如果交货日期在开始日期后的十四(14)天内，则交货日期为黄色的．DELIVERY DATE单元格的颜色也可以更改为黄色吗?

3.在状态，如果交货日期在开始日期后的二十一(21)天内，则交货日期为绿色．DELIVERY DATE单元格的颜色也可以更改为绿色吗?

4.在状态，如果交货日期是在开始日期的二十八(28)天内，则交货日期为蓝色的．DELIVERY DATE单元格的颜色也可以更改为蓝色吗?

我的日期状态列在每个单元格中有以下下拉菜单选项:硬日期，计划日期，占位日期

我还试图使用以下语句来改变我的甘特视图以及我的日历视图中持续时间栏的颜色:

1.在日期状态，如果选择硬日期，则日历持续时间条为蓝色。

2.在日期状态，如果选择了SCHEDULED，则日历持续时间栏为黄色。

3.在日期状态，如果选择了占位符，则日历持续时间栏为绿色。

谢谢你！

杰夫

标签:
· 在Facebook上分享 在Twitter上分享

最佳答案

  • 杰夫·M。
    杰夫·M。 ✭✭✭✭
    ✓回答

    选择页面顶部的条件格式选项，你就可以按照你输入的方式进行设置。当选择状态栏并将条件设置为“绿色”时，只需选择格式以突出显示“Delivery Date”单元格的绿色即可。日历也应该显示格式。

    · 在Facebook上分享 在Twitter上分享

答案

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

在智能表中创建和编辑公式

交叉表引用的公式组合

智能表功能列表

类别

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

请查看公式手册模板!
Try this:<\/p>

=IF([% Spend]@row> 0.89, \"Hundred\", IF([% Spend]@row> 0.7, \"Seventy\", \"NO\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-01-18 16:33:59","updateUser":{"userID":148602,"name":"Mike B","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20B","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-18T20:14:03+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":[]},{"discussionID":99808,"type":"question","name":"Combining 2 IF statements","excerpt":"I have 2 valid IF statements but I would like to combine them so the value from either statement is returned in one cell. Below are the 2 statements =IF([New Priority]@row >= 14, \"Level 1\", IF([New Priority]@row <= 9, \"Level 3\") =IF(AND([New Priority]@row >= 10, [New Priority]@row <= 13), \"Level 2\")","categoryID":322,"dateInserted":"2023-01-17T23:25:36+00:00","dateUpdated":null,"dateLastComment":"2023-01-18T18:48:15+00:00","insertUserID":156970,"insertUser":{"userID":156970,"name":"J Hogan","url":"https:\/\/community.smartsheet.com\/profile\/J%20Hogan","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-18T18:47:43+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":156970,"lastUser":{"userID":156970,"name":"J Hogan","url":"https:\/\/community.smartsheet.com\/profile\/J%20Hogan","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-18T18:47:43+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":29,"score":null,"hot":3348066831,"url":"https:\/\/community.smartsheet.com\/discussion\/99808\/combining-2-if-statements","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/99808\/combining-2-if-statements","format":"Rich","lastPost":{"discussionID":99808,"commentID":357608,"name":"Re: Combining 2 IF statements","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/357608#Comment_357608","dateInserted":"2023-01-18T18:48:15+00:00","insertUserID":156970,"insertUser":{"userID":156970,"name":"J Hogan","url":"https:\/\/community.smartsheet.com\/profile\/J%20Hogan","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-18T18:47:43+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-01-18T18:47:48+00:00","dateAnswered":"2023-01-18T03:47:39+00:00","acceptedAnswers":[{"commentID":357485,"body":"

Hi, @J Hogan<\/a>, if you include all parts (full syntax) of the IF()<\/strong> expression in your first formula and layout the formula like below...<\/p>

= IF([New Priority]@row >= 14\n\t, \"Level 1\"\n\t, IF([New Priority]@row <= 9\n\t      , \"Level 3\"\n              , \"xxxx\"\n             )\n\t)\n<\/pre>
...then you can substitute your second formula\/expression for the \"xxxx\" resulting in...<\/p>
= IF([New Priority]@row >= 14, \"Level 1\", IF([New Priority]@row <= 9, \"Level 3\", IF(AND([New Priority]@row >= 10, [New Priority]@row <= 13), \"Level 2\")))<\/code><\/p>
However, you needn't do this since... If \"New Priority\" is not ≥ 14 AND <\/strong>it is not ≤ 9, that is to say, \"New Priority\" is between 9 and 14, then it's a \"Level 2\". For this, simply replace \"xxxx\" with \"Level 2\".<\/p>
= IF([New Priority]@row >= 14\n\t, \"Level 1\"\n\t, IF([New Priority]@row <= 9\n\t      , \"Level 3\"\n              , \"Level 2\"\n             )\n\t)\n<\/pre>
Collapsed, your shortened formula would be, <\/p>
= IF([New Priority]@row >= 14, \"Level 1\", IF([New Priority]@row <= 9, \"Level 3\", \"Level 2\"))<\/code><\/p>
https:\/\/help.smartsheet.com\/function\/if<\/a><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-01-18 18:47:48","updateUser":{"userID":156970,"name":"J Hogan","url":"https:\/\/community.smartsheet.com\/profile\/J%20Hogan","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-18T18:47:43+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":[]},{"discussionID":99807,"type":"question","name":"How to set a value in one column based on value of another column","excerpt":"I have 3 columns: \"auto complete fill\", \"status\", and \"% complete\" I set % complete manually, but would like to automatically set it to 100% when status = Complete. Since the formula get's overwritten when I manually set % complete, I created the helper column \"auto complete fill\" to hold a formula, hoping to use the…","categoryID":322,"dateInserted":"2023-01-17T23:00:16+00:00","dateUpdated":null,"dateLastComment":"2023-01-18T16:49:29+00:00","insertUserID":148275,"insertUser":{"userID":148275,"name":"Kurt Robohm","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kurt%20Robohm","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-18T21:57:46+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":148275,"lastUser":{"userID":148275,"name":"Kurt Robohm","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kurt%20Robohm","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-18T21:57:46+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":26,"score":null,"hot":3348058185,"url":"https:\/\/community.smartsheet.com\/discussion\/99807\/how-to-set-a-value-in-one-column-based-on-value-of-another-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/99807\/how-to-set-a-value-in-one-column-based-on-value-of-another-column","format":"Rich","lastPost":{"discussionID":99807,"commentID":357580,"name":"Re: How to set a value in one column based on value of another column","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/357580#Comment_357580","dateInserted":"2023-01-18T16:49:29+00:00","insertUserID":148275,"insertUser":{"userID":148275,"name":"Kurt Robohm","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kurt%20Robohm","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-18T21:57:46+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-01-18T16:49:48+00:00","dateAnswered":"2023-01-18T16:49:29+00:00","acceptedAnswers":[{"commentID":357580,"body":"
Perfecto!  Thank you.  Works great!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-01-18 16:49:48","updateUser":{"userID":148275,"name":"Kurt Robohm","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kurt%20Robohm","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-18T21:57:46+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":[]}">