条件格式

WillH
WillH ✭
在内容基本知识

你好,

我需要基于工作表中的另一行应用条件格式。较高的应以红色突出显示，等于或低于绿色突出显示。

image.png

在条件格式规则中使用目标数字很容易，但我需要能够经常更改它，而不更改所有格式规则……

这能做到吗?

谢谢

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

最佳答案

  • 卡森Penticuff
    卡森Penticuff ✭✭✭✭✭
    ✓回答

    @isabonita是在正确的轨道上。这是一个复杂的答案，但一旦你弄清楚了逻辑，它就没那么糟糕了。

    在条件格式中，您无法以一种方便的方式引用特定的单元格。例如，您不能说，如果这个特定单元格大于那个单元格，则应用此格式。您可以对同一行执行此操作，但不总是引用相同的单元格。我们通过为我们想要检查的每个条件插入一个“助手”列来解决这个问题。复选框格式工作得很好。在条件格式规则中，您可以简单地说，如果选中了此单元格，则将该格式应用于该单元格。

    要将复选框设置为选中/未选中，我们使用以下列公式:(电子邮件保护)> pdp #， 1,0)

    但是什么是PDP#呢?这是我们必须解决的另一个问题。不能引用列公式中列的第一个单元格，只能引用同一行中的单元格。为了解决这个问题，我们将字段添加到工作表摘要中。[PDP]#引用工作表摘要中的PDP字段。为了使更新这些值更容易，我们使用=PDP1将该字段的值设置为PDP列中的第一个单元格

    在创建所有辅助列、为每个列设置列公式以确定选中哪些列、在工作表摘要中创建所有必要字段并将这些字段设置为工作表中每个相应列中的第一个单元格之后，我们可以设置条件格式。

    看看下面的图片，了解一下如何设置格式。不幸的是，我们在这个阶段还有一个解决方案要设置。由于格式将应用于工作表中的每一行，因此它也将应用于第一行，并且所有这些单元格都将是绿色的。为了解决这个问题，将下图中的第一种格式添加为列表中的第一种格式。对于背景色，你必须选择“白色”，以避免高光。如果你将其设置为“无背景”，你会得到一个错误，因为Smartsheet没有看到“无背景”作为格式。

    在所有设置和运行之后，您应该能够更新目标行中的单元格，并且事情将自动解决。



    Condition.PNG

    我希望这能有所帮助，我真的希望这是有意义的。

    · 在Facebook上分享 在Twitter上分享

答案

  • isabonita
    isabonita ✭

    我会用Helper列做类似的事情，其中包含一个公式，可以进行我需要的计算。

    IF ([CBP2]@row > [CBP2]1， "红"，"绿")

    它总是会和目标行比较吗?然后使用[CBP2]$1(或任何行号)

    然后让条件格式查看正确的列，并仅用格式为该列(而不是整个行)上色

    您需要为每一列创建一个助手—这是我无法使用列公式的地方。将公式拖过工作表。

    也许有一些更好的方法来处理这个问题，所以我期待听到别人的想法!

    · 在Facebook上分享 在Twitter上分享
  • 卡森Penticuff
    卡森Penticuff ✭✭✭✭✭
    ✓回答

    @isabonita是在正确的轨道上。这是一个复杂的答案，但一旦你弄清楚了逻辑，它就没那么糟糕了。

    在条件格式中，您无法以一种方便的方式引用特定的单元格。例如，您不能说，如果这个特定单元格大于那个单元格，则应用此格式。您可以对同一行执行此操作，但不总是引用相同的单元格。我们通过为我们想要检查的每个条件插入一个“助手”列来解决这个问题。复选框格式工作得很好。在条件格式规则中，您可以简单地说，如果选中了此单元格，则将该格式应用于该单元格。

    要将复选框设置为选中/未选中，我们使用以下列公式:(电子邮件保护)> pdp #， 1,0)

    但是什么是PDP#呢?这是我们必须解决的另一个问题。不能引用列公式中列的第一个单元格，只能引用同一行中的单元格。为了解决这个问题，我们将字段添加到工作表摘要中。[PDP]#引用工作表摘要中的PDP字段。为了使更新这些值更容易，我们使用=PDP1将该字段的值设置为PDP列中的第一个单元格

    在创建所有辅助列、为每个列设置列公式以确定选中哪些列、在工作表摘要中创建所有必要字段并将这些字段设置为工作表中每个相应列中的第一个单元格之后，我们可以设置条件格式。

    看看下面的图片，了解一下如何设置格式。不幸的是，我们在这个阶段还有一个解决方案要设置。由于格式将应用于工作表中的每一行，因此它也将应用于第一行，并且所有这些单元格都将是绿色的。为了解决这个问题，将下图中的第一种格式添加为列表中的第一种格式。对于背景色，你必须选择“白色”，以避免高光。如果你将其设置为“无背景”，你会得到一个错误，因为Smartsheet没有看到“无背景”作为格式。

    在所有设置和运行之后，您应该能够更新目标行中的单元格，并且事情将自动解决。



    Condition.PNG

    我希望这能有所帮助，我真的希望这是有意义的。

    · 在Facebook上分享 在Twitter上分享
  • isabonita
    isabonita ✭

    @Carson Penticuff——是的!工作表摘要字段!更好的…

    · 在Facebook上分享 在Twitter上分享
  • WillH
    WillH ✭

    谢谢你！@isabonita&@Carson Penticuff真是太棒了!

    · 在Facebook上分享 在Twitter上分享

类别

Is it set to allow for multiple selections? If so, reports cannot group by that. The only way to get it to work on a dropdown column is if it is set as a single select.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":106835,"type":"question","name":"Can I use Form collected Data to Pre-populate another form?","excerpt":"I have created a Credit App for our company... The form works great for collection of data. Once I have the data though, we need to follow up with Credit References, etc. What I would like to do is have a new form that is sent to someone to follow up with each of the references provided by our customers. What I would love…","categoryID":321,"dateInserted":"2023-06-23T15:12:05+00:00","dateUpdated":null,"dateLastComment":"2023-06-23T19:21:09+00:00","insertUserID":142957,"insertUser":{"userID":142957,"name":"PaulPerger","url":"https:\/\/community.smartsheet.com\/profile\/PaulPerger","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-23T19:18:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":142957,"lastUser":{"userID":142957,"name":"PaulPerger","url":"https:\/\/community.smartsheet.com\/profile\/PaulPerger","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-23T19:18:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":28,"score":null,"hot":3375084794,"url":"https:\/\/community.smartsheet.com\/discussion\/106835\/can-i-use-form-collected-data-to-pre-populate-another-form","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106835\/can-i-use-form-collected-data-to-pre-populate-another-form","format":"Rich","lastPost":{"discussionID":106835,"commentID":382087,"name":"Re: Can I use Form collected Data to Pre-populate another form?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382087#Comment_382087","dateInserted":"2023-06-23T19:21:09+00:00","insertUserID":142957,"insertUser":{"userID":142957,"name":"PaulPerger","url":"https:\/\/community.smartsheet.com\/profile\/PaulPerger","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-23T19:18:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-23T19:18:53+00:00","dateAnswered":"2023-06-23T19:12:52+00:00","acceptedAnswers":[{"commentID":382086,"body":"

It depends on your automation settings. If you set it to unrestricted, you can send to an email address in a cell without giving them access to the sheet. You can also specify which cells are provided in the update request within the automation builder.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":106840,"type":"question","name":"Report not showing all rows that match the filter parameters","excerpt":"Hello! I have a sheet that has 3424 rows with a specific Due Date. When I look at that filter option in the report, I see that it sees 3424 for that field. However, when I add that parameter, only 2500 rows display. I can't find a row limit to reports that would cause this. Is there a row limit to reports that I'm unaware…","categoryID":321,"dateInserted":"2023-06-23T16:50:36+00:00","dateUpdated":null,"dateLastComment":"2023-06-23T17:36:10+00:00","insertUserID":74136,"insertUser":{"userID":74136,"name":"asimcock","url":"https:\/\/community.smartsheet.com\/profile\/asimcock","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-23T17:45:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-23T19:11:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":33,"score":null,"hot":3375083206,"url":"https:\/\/community.smartsheet.com\/discussion\/106840\/report-not-showing-all-rows-that-match-the-filter-parameters","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106840\/report-not-showing-all-rows-that-match-the-filter-parameters","format":"Rich","tagIDs":[265],"lastPost":{"discussionID":106840,"commentID":382043,"name":"Re: Report not showing all rows that match the filter parameters","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382043#Comment_382043","dateInserted":"2023-06-23T17:36:10+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-23T19:11:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-23T17:47:12+00:00","dateAnswered":"2023-06-23T17:02:03+00:00","acceptedAnswers":[{"commentID":382031,"body":"

See this article. There is a 2500 row limit for reports.<\/p>

https:\/\/help.smartsheet.com\/articles\/2482082-configure-grouping-to-organize-results-in-report-builder<\/a><\/p>"},{"commentID":382036,"body":"

That is only for grouping. If you are not grouping, there should be a small link if you scroll all the way to the bottom of the report to go to the next page.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":265,"urlcode":"Reports","name":"Reports"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=341&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":5404,"limit":3},"title":"Trending in Using Smartsheet","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

使用Smartsheet的趋势

  • “报告中的分组”中没有显示所有列

    我对Smartsheet很陌生，不知道我在这里做错了什么。我试图通过“PM”列分组，但它没有出现在列表中。它的格式为下拉列表，但列表中出现的其他列的格式也相同。我做错了什么?谢谢!

  • 我可否使用表格收集的资料预先填入另一个表格?

    我为我们公司创建了一个信用应用程序…这个表单非常适合收集数据。一旦我有了数据，我们需要跟进信用参考等。我想做的是有一个新的表格，发送给某人跟进我们的客户提供的每一个参考。我想要的是……

  • 报告未显示与筛选器参数匹配的所有行

    你好!我有一个表，有3424行，有一个特定的截止日期。当我查看报告中的过滤器选项时，我看到该字段显示为3424。但是，当我添加该参数时，只显示2500行。我找不到会导致这种情况的报告行限制。有没有我不知道的报告行数限制?