checkbox formulas for Excel 2011 on Mac

[newUser1987]

Hey guys,

I have a checklist sheet that I was making and there are groups of 10 tasks under a major heading. There is a checkbox to the right of all 11 rows. I wanted to check the box and turn that cell green if it's checked and then be red if unchecked. If you checked the major heading row it would check all of the boxes in that group (all 11) and turn them all green. Then if you check any single box it would update another field for the % complete.

Does anyone have any experience doing something like this? I've been playing around with it but I haven't been successful in getting it working. I've been trying things like adding a macro to each check box and then having it add 2% to the "%complete" cell because there are 50 tasks in total, but this hasn't been working properly and seems prone to failure since it will break the formula if you add a new task to the sheet.

Anyone have any ideas about how to accomplish this in Excel for Mac?

 

[newUser1987]

Okay, I found the first couple of things through trial and error and they seem to work pretty well. For the formatting red/green you have to right-click on the checkbox and click "Format Control...", then specify a cell that's next to it in a new column that will contain the formula. In my case the checkboxes were in row F, so for F6 checkbox I put in G6 for the "Cell Link". Then in G6 I went to "Conditional Formatting" and added a new rule for the red and another for the green. (Style is classic in conditional formatting and then "use a formula to determine...", =G6=TRUE, then pick the color you want)
Then make one more rule and apply it to check box for the false behavior. Once that's done you can use the format paintbrush to copy it to every other checkbox.

The percentage done part required a hidden row as well where I took all the checkboxes I had (49) and divided that into 100 to get 2.04% that each one would increase or decrease the total. Then I click on the first cell in the new row and put in "=IF(G6=TRUE,A1+2.04%,A1)". So will look at G6 and if the box has been checked and it's TRUE it will add 2.04% to the % complete box, but if not it just leaves it at the current value of A1. Then can be copied and pasted to the other cells and just change the value of the cell to G7, G8, G9, etc.

I haven't found a clean way to check all the boxes for the master one though. The code that I've found to do it doesn't seem to work from other places I've found