Excel VBA checklist resulting

[proskopos]

I have the spreadsheet above, that describes a job's Income, outcome, and profit. It also shows if an employee took place in that job or not, in order to calculate his income. In the demo above, for the 1st job employees A,B,C will share 70$, and for the 2nd job A and C will share 140$. That means (for the second table) that A = 70/3 +14/2 = 93.3, b = 70/3 + 0 = 23.3 etc.
So if someone has taken part in a job, i have to choose the appropriate box, to calculate his income.
The question is what should i do, in order to auto calculate the employees income, when his checkbox is null or not for every job.

(Checkboxes are activeX control checkboxes and in each row there are grouped)

 

[Snakehips]

proskopos,

Welcome to MrExcel.

Here is a possible solution that will avoid having to use vba.

It may well depend on the extent of your data as I can imagine that you might be dealing with a lot of check boxes?/

So...

In the checkbox properties, set the linked cell to the cell in wich it sits.
The cell wil then take on value TRUE if checkbox is checked.
The cells can have text set to White so as to make it 'invisible'.

Then perhaps a 'helper' cell eg J (which could be hidden) that holds the profit per employee for the job.

Then formula in K5 and copy accross.


Excel 2007

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
Helper
Ref	Job	In	Out	Profit	A	B	C	D	Per Emp
Test
Test2

<COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0"><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="bgcolor: #ffff00"]A[/TD]
[TD="bgcolor: #ffff00"]B[/TD]
[TD="bgcolor: #ffff00"]C[/TD]
[TD="bgcolor: #ffff00"]D[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"]23.33333[/TD]
[TD="bgcolor: #ffff00, align: right"]93.33[/TD]
[TD="bgcolor: #ffff00, align: right"]23.33[/TD]
[TD="bgcolor: #ffff00, align: right"]93.33[/TD]
[TD="bgcolor: #ffff00, align: right"]0.00[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]200[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"]70[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</TBODY>

Sheet4​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #e0e0f0"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #e0e0f0"]J5[/TH]
[TD="align: left"]=E5/COUNTIF(F5:I5,TRUE)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]K5[/TH]
[TD="align: left"]=SUMIF(F5:F6,TRUE,$J5:$J6)[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]


My example range is only two rows deep so adjust to suit.
Hope that helps.

 

[proskopos]

that is an excellent solution, thank you very much. It works great for me.

If it possible i would like to see how to make it, with the checklists. It seems more pro..

 

[Snakehips]

that is an excellent solution, thank you very much. It works great for me.

If it possible i would like to see how to make it, with the checklists. It seems more pro..

Am I missing something?

When you say checklists I assume you mean check boxes ie check boxes in columns F G H I ?

My example shows no check boxes but assumes that they are there and are generating the TRUEs shown. s I tried to explain….

If you select each of your check boxes, in turn, select properties and set the 'Linked Cell' property to the address of the cell that the checkbox is in e.g. $F$5, $G$5 etc.
Then when you check or uncheck the checkbox the linked cell value will change automatically. Make the text of those cells White and it will not be seen but the values will allow the formulas to calculate your desired result.

 

[proskopos]

You are not missing anything! I just didnt know the true/false "function" of the checkbox.

I did it and it works perfect! Thank you very much.

Consider the ticket, closed!