Hi,
I have 2 tables in 2 sheets.
Sheet1
[TABLE="width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]Margin MTD[/TD]
[TD]Target MTD[/TD]
[TD]Margin QTD[/TD]
[TD]Target QTD[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Russell[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Conrad[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Denis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sheet2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Quarter[/TD]
[TD]Month[/TD]
[TD]Day[/TD]
[TD]Name[/TD]
[TD]Margin[/TD]
[TD]Target[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]01-05-2018[/TD]
[TD]James[/TD]
[TD]10[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]01-05-2018[/TD]
[TD]Mark[/TD]
[TD]20[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]01-05-2018 [/TD]
[TD]Frank[/TD]
[TD]25[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]01-05-2018 [/TD]
[TD]Jim[/TD]
[TD]31[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]02-05-2018 [/TD]
[TD]James[/TD]
[TD]23[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]02-05-2018 [/TD]
[TD]Mark[/TD]
[TD]4[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]02-05-2018 [/TD]
[TD]Frank[/TD]
[TD]100[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]02-05-2018 [/TD]
[TD]Jim[/TD]
[TD]32[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]02-05-2018[/TD]
[TD]Russell[/TD]
[TD]11[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]02-05-2018[/TD]
[TD]Conrad[/TD]
[TD]23[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]03-05-2018[/TD]
[TD]Mark[/TD]
[TD][/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]03-05-2018[/TD]
[TD]Denis[/TD]
[TD][/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]June[/TD]
[TD]05-06-2018[/TD]
[TD]Mark[/TD]
[TD][/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]June[/TD]
[TD]05-06-2018[/TD]
[TD]Frank[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]June[/TD]
[TD]05-06-2018[/TD]
[TD]Jim[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]June[/TD]
[TD]05-06-2018[/TD]
[TD]James[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
Result:
In B2 (Table Sheet1), I would like to have: sum column F (Table sheet2), <= Today date (dynamic), if column E (Table sheet2) is matching column A (Table sheet1) and it is the current month.
In C2 (table Sheet1), same per above, but sum of column G (Table sheet2)
In D2 (Table Sheet1), I would like to have: sum column F (Table sheet2), <= Today date (dynamic), if column E (Table sheet2) is matching column A (Table sheet1) and it is the current quarter or Q2.
In E2 (table Sheet1), same per above, but sum of column G (Table sheet2)
I tried to use helper column/cell but I am not able to find a solution by myself. Any help?
Thank you
I have 2 tables in 2 sheets.
Sheet1
[TABLE="width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]Margin MTD[/TD]
[TD]Target MTD[/TD]
[TD]Margin QTD[/TD]
[TD]Target QTD[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Russell[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Conrad[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Denis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sheet2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Quarter[/TD]
[TD]Month[/TD]
[TD]Day[/TD]
[TD]Name[/TD]
[TD]Margin[/TD]
[TD]Target[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]01-05-2018[/TD]
[TD]James[/TD]
[TD]10[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]01-05-2018[/TD]
[TD]Mark[/TD]
[TD]20[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]01-05-2018 [/TD]
[TD]Frank[/TD]
[TD]25[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]01-05-2018 [/TD]
[TD]Jim[/TD]
[TD]31[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]02-05-2018 [/TD]
[TD]James[/TD]
[TD]23[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]02-05-2018 [/TD]
[TD]Mark[/TD]
[TD]4[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]02-05-2018 [/TD]
[TD]Frank[/TD]
[TD]100[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]02-05-2018 [/TD]
[TD]Jim[/TD]
[TD]32[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]02-05-2018[/TD]
[TD]Russell[/TD]
[TD]11[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]02-05-2018[/TD]
[TD]Conrad[/TD]
[TD]23[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]03-05-2018[/TD]
[TD]Mark[/TD]
[TD][/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]03-05-2018[/TD]
[TD]Denis[/TD]
[TD][/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]June[/TD]
[TD]05-06-2018[/TD]
[TD]Mark[/TD]
[TD][/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]June[/TD]
[TD]05-06-2018[/TD]
[TD]Frank[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]June[/TD]
[TD]05-06-2018[/TD]
[TD]Jim[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]June[/TD]
[TD]05-06-2018[/TD]
[TD]James[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
Result:
In B2 (Table Sheet1), I would like to have: sum column F (Table sheet2), <= Today date (dynamic), if column E (Table sheet2) is matching column A (Table sheet1) and it is the current month.
In C2 (table Sheet1), same per above, but sum of column G (Table sheet2)
In D2 (Table Sheet1), I would like to have: sum column F (Table sheet2), <= Today date (dynamic), if column E (Table sheet2) is matching column A (Table sheet1) and it is the current quarter or Q2.
In E2 (table Sheet1), same per above, but sum of column G (Table sheet2)
I tried to use helper column/cell but I am not able to find a solution by myself. Any help?
Thank you