Creating a Task Completion Summary sheet using formulas from a copy/paste dump

[mradrenaline]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Monday
[/TD]
[TD]Tuesday
[/TD]
[TD]Wednesday
[/TD]
[TD]Thursday
[/TD]
[TD]-
[/TD]
[TD]Completed
[/TD]
[TD]Goal
[/TD]
[TD]Percent[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Leader[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]100%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Boss1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]25%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Boss2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]0%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sheet 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 650"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]N/A
[/TD]
[TD]N/A
[/TD]
[TD]Submit Date
[/TD]
[TD]N/A
[/TD]
[TD]N/A
[/TD]
[TD]N/A[/TD]
[TD]Owner[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]6-Nov-17[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]Leader[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]6-Nov-17[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]Leader[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]6-Nov-17[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]Leader[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]6-Nov-17[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]Boss1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]7-Nov-17[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]Leader[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Sheet 2 is how information populates if I copy/paste it into excel from the internet. Sheet 1 is the desired format for the Task Completion Summary sheet. I need the formulas to get this information from the copy/paste format into sheet 1 with the shown format. I'm able to set up the conditional formatting, but I don't know how to get excel to recognize the day of the week from the date shown in sheet 2 and auto-count it for each person into sheet 1 based on the day of the week. I need the information from sheet 2 to be translated to the summary report from sheet 1 using formulas. Any help is much appreciated.

 

[Truiz]

I think this can be accomplished like this

Excel 2012

	A	B	C	D	E	F	G	H	I	J

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]Friday[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]Completed[/TD]
[TD="align: center"]Goal[/TD]
[TD="align: center"]Percent[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Leader[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]100%[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Boss1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]25%[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Boss2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]0%[/TD]

</tbody>

Hoja3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$2)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$2)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$2)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$2)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$2)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$3)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]=COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$3)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$3)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]=COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$3)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F3[/TH]
[TD="align: left"]=COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$3)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$4)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]=COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$4)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]=COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$4)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E4[/TH]
[TD="align: left"]=COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$4)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F4[/TH]
[TD="align: left"]=COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$4)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=SUM(B2:F2)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]J2[/TH]
[TD="align: left"]=H2/I2[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]J3[/TH]
[TD="align: left"]=H3/I3[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]J4[/TH]
[TD="align: left"]=H4/I4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



As you can see I added a helper column in the data and used a formula to get a value for the day in the date


Excel 2012

	A	B	C	D	E	F	G	H
N/A	N/A	Submit Date	N/A	N/A	N/A	Owner
N/A	N/A	N/A	N/A	N/A	Leader
N/A	N/A	N/A	N/A	N/A	Leader
N/A	N/A	N/A	N/A	N/A	Leader
N/A	N/A	N/A	N/A	N/A	Boss1
N/A	N/A	N/A	N/A	N/A	Leader

<tbody>
[TD="align: center"]1[/TD]

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

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

[TD="align: right"]6-Nov-17[/TD]
[TD="align: right"]2[/TD]

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

[TD="align: right"]6-Nov-17[/TD]
[TD="align: right"]2[/TD]

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

[TD="align: right"]6-Nov-17[/TD]
[TD="align: right"]2[/TD]

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

[TD="align: right"]6-Nov-17[/TD]
[TD="align: right"]2[/TD]

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

[TD="align: right"]7-Nov-17[/TD]
[TD="align: right"]3[/TD]

</tbody>

Hoja4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=WEEKDAY(C2)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=WEEKDAY(C3)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]=WEEKDAY(C4)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D5[/TH]
[TD="align: left"]=WEEKDAY(C5)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D6[/TH]
[TD="align: left"]=WEEKDAY(C6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Sunday = 1
Monday = 2
Tuesday = 3

and so on