I've got a spreadsheet where people enter in values into columns A through H with I being a checkbox. On another sheet, it keeps a running total of all of the values of column E based on the values of the cells in column G and I.
Right now I have to manually go through and check each box in column I to reduce the total. I'm wondering if I can make it so I just have to check 1 box and it will check all of the cells in column G and the values of the check boxes in column I and if cell in G equals one thing and the value of I equals FALSE, then it changes the value of I to TRUE.

Cells in column G of Sheet1...1=Alex, 2=Johnnie, 3=AL.
Cells in column I of Sheet1...1=TRUE, 2=FALSE, 3=FALSE...
I set cell C2 in Sheet2 to TRUE and Sheet1 I2 is set to TRUE but the others are left unchanged.

  • Your question is a little unclear. Maybe a screenshot would help. Do you want one checkbox that will change the value of other checkboxes according to a formula?
    – Jacktose
    Oct 30, 2018 at 16:42
  • Yes. So, on one sheet, I want to manually check off a box, and it will automatically set the value of every check box on another sheet, if the name matches and the box is FALSE. So, manually check off the "Paid Up?" box - i.gyazo.com/e964dd0681c9e6da9d07eddb23191961.png And that will go through and check off the boxes in another sheet that match the name and aren't checked off. i.gyazo.com/fee7cb904ecfe527a44185c19e253145.png
    – Opt1kal
    Oct 30, 2018 at 22:10

1 Answer 1


I tried to base this on your screenshots in your comment.

example sheet example sheet with formulas

I inserted checkboxes in both places (pretend the right side is your other sheet, fix the references accordingly). Then I entered a VLOOKUP() formula where you want the value to change. It will set each box to checked if the corresponding name on the left side has a check next to it.

Caveats: The names must match. There should be exactly one of each name on the left. You can no longer manually check the boxes on the right.

If I've understood you correctly, I hope that helps.
If what you're looking for is more of a one-time pass to check the boxes like you would do manually, then you can't do that with formulas. You're going to need to write a script.

  • Awesome. Looks like what I'm needing...thank you! I'll plug it in and see. Thanks again!
    – Opt1kal
    Oct 31, 2018 at 0:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.