Dynamically (un)set a checkbox with a formula

Question

Level 1

8 points

Dynamically (un)set a checkbox with a formula

I know this feature is currently not available, however, I do wonder why not. I'd like to set or unset a checkbox by assigning a formula to the checkbox that returns TRUE or FALSE. If I first create a cell with the formula I want, it will toggle between TRUE and FALSE as I change the inputs to the formula. However, when I change the data format of the cell to checkbox the checkbox is checked if the current value is TRUE (or unchecked if it is FALSE). From that point onward the checkbox is static and only changes to manual input. Switching back to automatic formatting presents either TRUE or FALSE values. The original boolean expression (formula) is lost.

I know there all sorts of kluge workarounds to get something roughly equivalent visually, but none of those (character approximations) are easily used in subsequent boolean calculations.

So my question is, Why can't the Apple Numbers team implement a dynamic checkbox by allowing an underlying formula? Or implement, a slightly different checkbox that locks out manual toggling?

Posted on Dec 19, 2022 7:27 AM

Reply

Answer 1

SGIII

Level 8

37,550 points

Dec 19, 2022 8:04 AM in response to JMZawodny

None of us here are privy to internal design decisions by Apple.

However, as a user, I am curious about what you are trying to accomplish.

As you know in any cell you can either have a value you input or a formula that displays a value. That's just how spreadsheets work. Checkbox is no different.

The Checkbox Data Format is really handy for toggling the value in a cell between TRUE and FALSE. It's for input.

You can use a simple formula with an IF function and a checkmark symbol to display a checkmark when conditions in other cells are satisfied.

If you feel this behavior should be modified you could provide your use case to Apple via Numbers > Provide Numbers Feedback in your menu.

SG

Reply

Answer 2

SGIII

Level 8

37,550 points

Dec 19, 2022 8:33 AM in response to JMZawodny

JMZawodny wrote:

It would be very nice if there was a mechanism to represent TRUE or FALSE in a visually appealing and familiar way rather than the sterile options of TRUE, 1 or FALSE, 0. A checked or unchecked checkbox is ideal even it had to be visually greyed out and immune to manual input. I want to keep the underlying value separate and accessible from the appearance.

I like displaying TRUE and FALSE as the result of calculations because they are clear and (I think) universally used in all spreadsheet apps. But displaying something different via formula is possible too.

As posted, you can try giving feedback to Apple via Numbers > Provide Numbers Feedback in your menu.

SG

Reply

Answer 3

JMZawodny Author

Level 1

8 points

Dec 19, 2022 8:26 AM in response to SGIII

Thanks for the prompt reply. The issue is a combination of visual aesthetics and ease of spreadsheet maintenance. Sure I can set a cell value by if(boolean expression,"this", "that") to get something approximating a checkbox. I can tailor the "this" and "that" to give me the look required (well sort of), but then the rest of the code has to work with whatever the values of "this" or "that" are. The underlying boolean is effectively lost and could be very difficult to replicate in other cells that depend upon the value. Maintenance becomes an issue if "this" and "that" are changed to "something" and "something else" through the review and revision process. The only real option is to have a table of parameters that include the current values for "this" and "that" hidden away somewhere in the spreadsheet.

It would be very nice if there was a mechanism to represent TRUE or FALSE in a visually appealing and familiar way rather than the sterile options of TRUE, 1 or FALSE, 0. A checked or unchecked checkbox is ideal even it had to be visually greyed out and immune to manual input. I want to keep the underlying value separate and accessible from the appearance.

Reply

Dynamically (un)set a checkbox with a formula

Similar questions