Check a checkbox based on status of other checkbox

Question

Level 1

9 points

Check a checkbox based on status of other checkbox

Hi, I am currently working on a checklist in Numbers that has two sheets.

One sheet is for clients to check options that they want, the other is the administrative view, that offers the same options with more detailed text next to it.

There are checkboxes next to every option on both sheets, now I need to "synchronise" the check state between certain boxes, so that when a client checks that they want option "X" with its flashy marketing title in sheet A, the technician can see the corresponding checkbox in sheet B checked as well and see the details on how to set up option "X".

How can this be achieved?

MacBook Pro with Retina display, macOS Sierra (10.12.3)

Posted on Mar 27, 2017 2:10 AM

Reply

Answer 1

Best reply

To_Mi

Level 4

1,202 points

Mar 27, 2017 7:57 AM in response to MUGCC

I do the (almost) same way as Wayne

for quick,

=IF(expression,"X","-")

for pretty looking,

=IF(expression,"✔⃣","　⃣")

Here is an example how to make.

✔⃣ = ( HEAVY CHECK MARK : U+2714 ) + ( COMBINING ENCLOSING KEYCAP : U+20E3 )

And some more

Note:

The actual visual may differ if different font is selected.
U+XXXX is a representation of Unicode XXXX

Also, here is another way for the case the cell must hold the value but show checkbox (look-alike).

Make the value as 0 or 1, not false or true.
Use custom format (number) with rule to show the preferred text based on the vale

Reply

Answer 2

Mar 27, 2017 7:57 AM in response to MUGCC

On the sheet where the check boxes are to be mirrored:

=if(cell = TRUE, "X", "")

where cell is the cell reference of the cell with the original checkbox

you could also replace the "X" with the symbol from those in the list of items in the menu item

"Edit > Emojis and Symbols"

There are lot's of checks.

Reply

Answer 3

MUGCC Author

Level 1

9 points

Mar 27, 2017 5:57 AM in response to To_Mi

That does indeed work technically.

Sheet B, being for experts, has more checkboxes than Sheet A and the convenience of toggling those quickly is great. Having visual and functional inconsistencies between real checkboxes and emojis isn't ideal.

On the topic of IF statements, I already tried something along the lines of:

=if(SheetA!B2 = TRUE, TRUE, FALSE)

in SheetB!B2, then formatted it to be a checkbox cell and it works once, but the moment the cell is reformatted from the text TRUE to the checkbox, it loses its formula.

The ideal case would be to actually have both the checkboxes sync up, i.e.

SheetA!B2 gets checked, SheetB!B2 checks itself;

SheetB!B2 gets checked, SheetA!B2 checks itself.

But this type of circular logic probably doesn't work in spreadsheets either.

Is there any way of using helper cells to manipulate other cells? Like:

=if(SheetA!B2 = TRUE, SheetB!B2 := TRUE, SheetB!B2 := FALSE)

Reply

Answer 4

MUGCC Author

Level 1

9 points

Mar 27, 2017 8:05 AM in response to Wayne Contello

Fair enough.

I'll test out, whether there is an emoji checkmark that is visually appealing, otherwise I'll probably go with conditional formatting and some colour scheme for the whole ordeal. Surely, technicians are more likely to be able to manually set a cell to green to match another one's colour than they are to match a certain checkmark buried deep in the emoji list if and when time comes to manually add an option.

Thank you both for your insight, help and time.

Much appreciated.

Reply