Excel Gurus, need some formula help.

[GoingUp]

Making a spreadsheet to track some project progress.

The project consists of installing 600 computers. Each computer will get its own line.

I have 4 columns that I want to put check boxes in. Each column will represent part of the job complete, such as computer in place, power in place, network in place etc.

The 4 columns are G, H, I & J. The percentage complete of the tasks in that order are 15%, 15%, 35%, 35%.

The forumula should sum all of the check boxes that are clicked. So if just H is checked, then I get a total of 15%. If H and J are both checked then I should get a total of 50%.

Anyone know how to do this at all? Also looking for a way to add check boxes to each of the excel cells in those 4 columns.

Thanks! 🙂

 

[theknight571]

Well one way would be to use the attached fomula, and use 'x' in the columns for checks.

 

[Kyteland]

Checkboxes are ActiveX controls, and I don't think they can be embedded in to specific cells. The best you will get is to position them on the page approximately where you want them. If that's the case you might as well open up the vba editor and do the whole thing in a macro, using the worksheets like a database.

In my opinion you're making this too complicated. KISS - Keep It Simple Stupid. 😉 You can create a dropdown list in those cells by clicking on Data->Validation. In the Settings tab select List from the Allow dropdown. In the Source box select a range of cells where you will place your options, like "Complete", "Not Complete", or whatever. If you do this, the cell can only contain data from your list, which makes your total formula simple.

 

[theknight571]

Ok... a little research... and a little experimentation later:

If G,H,I,J are going to be checkboxes for the entire length of the spreadsheet then this shold work for ya. 🙂

Attached is the new formula for the spreadsheet that will "total" up the checkboxes.

Also attached is code that will allow you to double click on a cell in one of the afore mentioned columns to treat each cell like a checkbox.

To add he code to the worksheet, right click on the worksheet's name tab and select View Code, then paste the code into that window.