[Calc 7.5.2.2] Linking cells to both controls and formulae without overwriting either? And/or writing the results of formulae to other cells?

[janomie]

I am trying to make a sheet where users can both enter and check various “animals”, which will then show the results of some formulae.

If you click the checkbox next to “cat”, it will show some related values. You can type “cat” into the cell indicated by “Animals”, and a formula in the Records sheet will return TRUE(). What I’m trying to do is allow both animals entered into the “Animals” cell to check (or uncheck if deleted) the associated checkbox AND let the user manually click and unclick checkboxes.

I can somewhat approximate by entering the “reading” formula directly into the cell linked to a checkbox. However, my problems with this method are 1) if the user manually clicks or unclicks a checkbox, the formula is overwritten with a TRUE or FALSE value and 2) for whatever reason, while this works if every step is performed in an open document, after opening a document that was saved with the formulae pre-entered into the relevant cells, the checkboxes no longer respond to the formulae results (it sometimes works, but not reliably; also, 1) is still an issue).

I’m a novice to Libreoffice Calc. I don’t really care how this is done, but so far it seems that writing the results for the formula which reads the “Animals” cell into the cells linked to respective checkboxes may be the most viable solution. I don’t know if I need to create a macro for this or if it’s possible with a formula.

Attached is a version with formulae already applied to the linked checkbox cells.
Noah’s Ark with Initial Formulae.ods (17.9 KB)

[fpy]

@janomie

don’t really care how this is done

I suspect the description of what you’re trying to achieve is a bit beyond the max focus span of contributors here
maybe you could try to rephrase in simple general terms.

ps.
I was brave enough to open your example, but reached my limits right away.
maybe the visual shot will inspire someone

[janomie]

In the ods named “Noah’s Ark with Initial Formulae”, if you type the name of an animal into E5, it will mark the associated checkbox in column H, and if you delete the name of of an animal, the box will uncheck. You can also manually check and uncheck the box as normal for that type of control. E.g. typing/deleting “cat” or “snake” into/from E5 will check/uncheck H9 and H16, respectively; you can also click the checks anchored to any cell in H manually. However, manually checking a box will overwrite the formula that allows typing an animal into E5 to check/uncheck the box.

I was attempting to preserve the functionality of E5 regardless if someone manually checks a box in H, which “breaks” the formula. I realize now that this was unnecessary, since the user may freely enter and delete items in E5, and the current set-up performs that exactly as intended.

The E5 functions (in Record.H8:17) work reliably now, for whatever reason (as mentioned in my previous post, they didn’t before), so I don’t really need the previously requested function/macro. It would be simpler to create a control button that they can press if they “break” any of the E5 functions in Record.H which overwrites the cells with the original functions. Though I am considering simply asking the user to reload the Calc document if they check a box without meaning to.

I’ve attached to the original question a reorganized (and hopefully more intuitive) spreadsheet.

[fpy]

@janomie

if you

(still very unclear what the actual use case is )