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.
Example:
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.
Best Answer
I tried to base this on your screenshots in your comment.
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.