How do i copy a checkbox value and keep the cell editable in Google Sheets?

I've posted this question 2 days ago but with no answers. Here's a link to a copy of the file I'm working on:

https://docs.google.com/spreadsheets/d/1TCQ0cWulb8Wm63Hs57ruwEXgpP1swKAi/edit?usp=sharing&ouid=110569351169109673921&rtpof=true&sd=true

Basically, what I've done is: the "effettivo" page is a copy of the "pianificazione" page, with all the cells in "effettivo" that copy its value from its relative. The trouble is: for the checkboxes on the right, the formula I've set (=pianificazione!G3, for example) copies the value but it renders the checkbox itself uneditable manually (= I can't click on the checkbox to change the value of that checkbox). I need to find a way to copy all those checkbox values from "pianificazione" to "effettivo" while mantaining the checkboxes themselves editable. Thanks again guys :)


Solution 1:

First of all the document you provided is not Google sheet, but Excel sheets. To make checkboxes (or in my example whole data) two-ways editable from both sheets and show the same values in both sheets everytime. I suggest you save this document as Google sheet and use GAS onEdit() function. With this function you also able to mirror other changes in values, not only checkboxes. I built fast example based on your document here

or you can just take over the function to your document

function onEdit(e) {
    var sheet = e.range.getSheet();
    var value = e.value;
    if (sheet.getName() == 'effettivo') {
        e.source.getSheetByName('pianificazione').getRange(e.range.rowStart, e.range.columnStart).setValue(value);
    }
    if (sheet.getName() == 'pianificazione') {
        e.source.getSheetByName('effettivo').getRange(e.range.rowStart, e.range.columnStart).setValue(value);
    }
}

Important! delete all formulas, that passes values between sheets, since that will be done by function, you don't need them anyway. Or just delete old 'effettivo' sheet and duplicate the 'pianificazione' and rename it to 'effettivo'. Also very important to authorize script first, best way to do it in Script editor trigger onEdit function manually and authorize it.