有条件地添加复选框

RyanGS

我有一个条件，我想有一个空白的单元格，直到任何东西被输入到同一行的单元格。

例如，如果第1列为空，则第2列中没有复选框。

如果在第1列中输入了任何数据，请在第2列中添加一个复选框。

不同之处在于，如果第1列为空，而数据输入到第3列，则不会出现复选框。

这可能吗?

尼克拉森

试试这个。如果第1列是空的，它将不选中该框。如果不是空的，打勾。

=如果(ISBLANK((第1列)@row， 0,1)

我不确定第三列要做什么但如果你展开一点，我可以把它加到这个公式中。

lmarchisio

我不确定这是否像你描述的那样可能。复选框是列如何格式化的函数。

您是否可以通过将其保留为文本/数字列并编写公式来使单元格保持空白，直到满足其他条件，然后添加0或1之类的数字作为触发其他公式的方式来解决此问题?

必须承认你对自己想要做的事情很好奇。我从来没有遇到过将复选框本身作为目标的情况。勾选和取消勾选，是的。但永远不会出现和消失。

她名叫Stara

嗨,瑞安,

是否要隐藏复选框以使单元格为空白?(没有正方形)

祝你周末愉快!

最好的

她名叫Stara

工作流程顾问@完成咨询

sean59916

你好,

补充尼克的回答——试着在第二栏找到你在问题中陈述的所有条件:

=IF(ISBLANK([Column1]1)， IF(ISBLANK([Column3]1)， 0,0)， 1)

我希望这对你有帮助。

肖恩

RyanGS

这是一种奇怪的情况。我在下面添加了一张更好的照片解释。

我不想添加一个复选框，我想添加一个能够被选中的复选框。这可能是不可能的。

保罗新来的

把这个放到你的复选框栏里…

=如果(ISBLANK((主要列)@row)，“。”，0)

然后，如果“主列”为“。”，则可以设置条件格式规则，将字体和单元格填充颜色都更改为白色。

将数据放入主列后，它将从一个空白单元格变为一个未选中的框。

记住……选中复选框后，它将从单元格中删除公式。

Gali本·

谢谢，保罗，我也有同样的需求，你的解决方案非常有效!

德

Put this in your checkbox column...<\/p>\n

 <\/p>\n

=IF(ISBLANK([Primary Column]@row)<\/a>, \".\", 0)<\/p>\n

 <\/p>\n

You can then set a Conditional Formatting rule to change the font and cell fill colors to both be white if Primary Column is \".\".<\/p>\n

 <\/p>\n

Once data is put into the Primary Column, it will change from what appears to be a blank cell to an unchecked box.<\/p>\n

 <\/p>\n

Keep in mind... Once the box is checked, it will delete the formula out of the cell.<\/p>","bodyRaw":"

Put this in your checkbox column...<\/p>\n\n

 <\/p>\n\n

=IF(ISBLANK([Primary Column]@row), \".\", 0)<\/p>\n\n

 <\/p>\n\n

You can then set a Conditional Formatting rule to change the font and cell fill colors to both be white if Primary Column is \".\".<\/p>\n\n

 <\/p>\n\n

Once data is put into the Primary Column, it will change from what appears to be a blank cell to an unchecked box.<\/p>\n\n

 <\/p>\n\n

Keep in mind... Once the box is checked, it will delete the formula out of the cell.<\/p>","format":"html","dateInserted":"2019-05-15T14:00:54+00:00","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-07T16:29:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/167656#Comment_167656","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/167656#Comment_167656

这个解决方案符合我的目的，但我注意到，如果我将公式变成列公式，我就不能再选中复选框了。复选框是否只能作为单元格公式使用?

保罗新来的

@LEllis当您手动输入数据(或选中某个框)时，它会从该单元格中完全删除公式。如果应用了列公式，然后手动更改了一个单元格，则它不再是列公式，因此它们会锁定该单元格，使其无法手动编辑。如果需要重写功能，则需要将其保留为单元格公式。

它与复选框没有太多关系，而是列公式与单元格公式的一般行为。

这实际上是一个相对较旧的线程虽然，所以可能有另一种方法，将更好地为您工作，现在有明确的单元格和改变单元格数据自动化。

你到底想做什么?您使用哪一列来隐藏或取消隐藏复选框?

德

@LEllis<\/a> When you manually enter data (or check a box), it removes the formula from that cell entirely. If you have a column formula applied and then change one cell manually, it is no longer a column formula, so they lock it down from being manually edited. If you want the ability to override, you would need to leave it as a cell formula.<\/p>

It isn't so much anything to do with checkboxes but how column formulas vs cell formulas in general act.<\/p>

This is actually a relatively old thread though, so there may be another approach that would work better for you now that there are clear cell and change cell data automations.<\/p>

What exactly is it you are wanting to do? What column are you using to either hide or unhide the checkbox?<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"LEllis\",\"userID\":157553}}},{\"insert\":\" When you manually enter data (or check a box), it removes the formula from that cell entirely. If you have a column formula applied and then change one cell manually, it is no longer a column formula, so they lock it down from being manually edited. If you want the ability to override, you would need to leave it as a cell formula.\\n\\nIt isn't so much anything to do with checkboxes but how column formulas vs cell formulas in general act.\\n\\nThis is actually a relatively old thread though, so there may be another approach that would work better for you now that there are clear cell and change cell data automations.\\n\\nWhat exactly is it you are wanting to do? What column are you using to either hide or unhide the checkbox?\\n\"}]","format":"rich","dateInserted":"2023-02-22T20:39:51+00:00","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-07T16:29:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/363705#Comment_363705","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/363705#Comment_363705

哦，当然，这完全说得通!

因此，我有三个复选框列来指示组需要在任务上签名的位置。签收的数量基于负责的组。大多数任务将有两个签名，但有些将有一个。选中复选框表示已完成签名。

以下是Smartsheet的编辑版本，使用本线程中描述的公式版本来显示/隐藏复选框。

我之所以对使用列公式感兴趣，是因为任务的数量可能会增加或减少。我从不同的表使用动态拉数据您的过滤解决方案,真的!因此，基本上，如果任务发生变化或者负责的小组发生变化，让复选框自行处理会很方便。我希望这能说得通。

我还没有深入研究自动化，但听起来我应该这么做。

保罗新来的

@LEllis您是否有一组逻辑可用于确定每行需要选中哪些框?如果是这样，我认为我们可以使用一个隐藏的助手列与公式有一些输出，以指示哪些框(es)不应该被选中。然后，您可以使用更改单元格自动化在不需要填充的适当列中放置一个“。”，并将其与条件格式组合在一起。

德

@LEllis<\/a> Do you have a set logic that can be used to determine which boxes need to be checked on each row? If so, I am thinking we can use a hidden helper column with a formula to have something output to indicate which box(es) should NOT be checked. Then you can use a change cell automation to put a "." in the appropriate column(s) that don't need filled and combine it with the conditional formatting.<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"LEllis\",\"userID\":157553}}},{\"insert\":\" Do you have a set logic that can be used to determine which boxes need to be checked on each row? If so, I am thinking we can use a hidden helper column with a formula to have something output to indicate which box(es) should NOT be checked. Then you can use a change cell automation to put a \\\".\\\" in the appropriate column(s) that don't need filled and combine it with the conditional formatting.\\n\"}]","format":"rich","dateInserted":"2023-02-23T14:55:43+00:00","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-07T16:29:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/363892#Comment_363892","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/363892#Comment_363892

是的，逻辑是:

如果[Group Responsible]包含A或B，则任务需要两个签名——一个来自A/B，另一个来自C。如果[Group Responsible]包含C组，则任务只需要C组签名。

换句话说:

A组接受以下签名:A和C。

B组签名:B和C。

C组签下如下签名:

德

@LEllis<\/a> Do you have a set logic that can be used to determine which boxes need to be checked on each row? If so, I am thinking we can use a hidden helper column with a formula to have something output to indicate which box(es) should NOT be checked. Then you can use a change cell automation to put a "." in the appropriate column(s) that don't need filled and combine it with the conditional formatting.<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"LEllis\",\"userID\":157553}}},{\"insert\":\" Do you have a set logic that can be used to determine which boxes need to be checked on each row? If so, I am thinking we can use a hidden helper column with a formula to have something output to indicate which box(es) should NOT be checked. Then you can use a change cell automation to put a \\\".\\\" in the appropriate column(s) that don't need filled and combine it with the conditional formatting.\\n\"}]","format":"rich","dateInserted":"2023-02-23T14:55:43+00:00","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-07T16:29:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/363892#Comment_363892","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/363892#Comment_363892

我能够建立一些非常简单的自动化工作流程，工作得很好!谢谢你给我指那个方向。

保罗新来的

@LEllis很乐意帮忙。️