Hi everyone!
I know the title is very generic but what I'm trying to do is hard to explain in a short sentence!
I'm working with multiple workbooks that I'm trying to combine into one workbook with multiple sheets for 2 reasons: 1-it will eliminate some double entry the client is doing, and 2-I'm trying to get the data streamlined for use in Power BI. Therefore I'm hoping I can find a way to do this, but I'm not sure its possible.
The first sheet (Billing Info) is a list of vendors and their invoice date, amount, project number, etc. The sheets after that are split up by project number (Project 1, Project 2, etc.) and show the PO's that were generated to match the invoices.
What I'd like to do is: anytime something is entered on the Billing Info sheet for Project 1 (or Project 2, etc.), copy data from columns B, C, D, and E (See Billing Info Sheet screenshot below) over to the Project 1 sheet (See Project 1 screenshot below - B into B, C into C, D into D, and E into A). BUT (And this is the tricky part) --- When bringing the data over to Project 1, I'd like to sum the amounts in Billing Info-Column D when the date in Billing Info-Column C (Invoice Date) are the same AND the number in Billing Info-Column A (Site) are the same.
For example:
Billing Info sheet:
[TABLE="width: 726"]
<tbody>[TR]
[TD]Line#[/TD]
[TD] A
SITE[/TD]
[TD] B
CONTRACTOR[/TD]
[TD] C
INVOICE DATE[/TD]
[TD] D
AMOUNT[/TD]
[TD] E
PROJECT[/TD]
[TD] F
PCA[/TD]
[TD] G
INVOICE[/TD]
[/TR]
[TR]
[TD]322[/TD]
[TD]703[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/05/2018[/TD]
[TD]$4,065.38[/TD]
[TD]427405-16[/TD]
[TD]51047[/TD]
[TD]R1840-02[/TD]
[/TR]
[TR]
[TD]323[/TD]
[TD]680[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/10/2018[/TD]
[TD]$10,279.03[/TD]
[TD]427405-16[/TD]
[TD]51047[/TD]
[TD]R1840-03[/TD]
[/TR]
[TR]
[TD]324[/TD]
[TD]703[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/10/2018[/TD]
[TD]$6,352.52[/TD]
[TD]427405-16[/TD]
[TD]51047[/TD]
[TD]R1840-03[/TD]
[/TR]
[TR]
[TD]325[/TD]
[TD]703[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/10/2018[/TD]
[TD]$3,813.38[/TD]
[TD]427405-16[/TD]
[TD]51047[/TD]
[TD]R1840-03[/TD]
[/TR]
[TR]
[TD]326[/TD]
[TD]703[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/17/2018[/TD]
[TD]$3,022.00[/TD]
[TD]427405-16[/TD]
[TD]51047[/TD]
[TD]R1840-04[/TD]
[/TR]
[TR]
[TD]327[/TD]
[TD]704[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/17/2018[/TD]
[TD]$18,601.38[/TD]
[TD]427405-16[/TD]
[TD]51047[/TD]
[TD]R1840-04[/TD]
[/TR]
[TR]
[TD]328[/TD]
[TD]680[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/25/2018[/TD]
[TD]$2,434.00[/TD]
[TD]427405-16[/TD]
[TD]51047[/TD]
[TD]R1840-05[/TD]
[/TR]
[TR]
[TD]329[/TD]
[TD]703[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/25/2018[/TD]
[TD]-$108.37[/TD]
[TD]427405-16[/TD]
[TD]51047[/TD]
[TD]R1840-05[/TD]
[/TR]
[TR]
[TD]330[/TD]
[TD]704[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/25/2018[/TD]
[TD]$27,277.51[/TD]
[TD]427405-16[/TD]
[TD]51047[/TD]
[TD]R1840-05[/TD]
[/TR]
[TR]
[TD]331[/TD]
[TD]676[/TD]
[TD]Western Pacific[/TD]
[TD]01/22/2019[/TD]
[TD]$8,175.50[/TD]
[TD]427405-36[/TD]
[TD]51064[/TD]
[TD]2236[/TD]
[/TR]
[TR]
[TD]332[/TD]
[TD]676[/TD]
[TD]Western Pacific[/TD]
[TD]01/30/2019[/TD]
[TD]$7,655.00[/TD]
[TD]427405-36[/TD]
[TD]51064[/TD]
[TD]2238[/TD]
[/TR]
[TR]
[TD]333[/TD]
[TD]676[/TD]
[TD]Western Pacific[/TD]
[TD]02/05/2019[/TD]
[TD]$5,954.50[/TD]
[TD]427405-36[/TD]
[TD]51064[/TD]
[TD]2239[/TD]
[/TR]
[TR]
[TD]334[/TD]
[TD]676[/TD]
[TD]Professional Reforestation[/TD]
[TD]02/19/2019[/TD]
[TD]$5,627.52[/TD]
[TD]427405-36[/TD]
[TD]51064[/TD]
[TD]R1840-26[/TD]
[/TR]
[TR]
[TD]335[/TD]
[TD]676[/TD]
[TD]Professional Reforestation[/TD]
[TD]03/06/2019[/TD]
[TD]$3,723.64[/TD]
[TD]427405-36[/TD]
[TD]51064[/TD]
[TD]R180-28[/TD]
[/TR]
[TR]
[TD]336[/TD]
[TD]676[/TD]
[TD]Professional Reforestation[/TD]
[TD]03/19/2019[/TD]
[TD]$2,524.88[/TD]
[TD]427405-36[/TD]
[TD]51064[/TD]
[TD]R180-29[/TD]
[/TR]
[TR]
[TD]337[/TD]
[TD]676[/TD]
[TD]Professional Reforestation[/TD]
[TD]03/26/2019[/TD]
[TD]$1,898.58[/TD]
[TD]427405-36[/TD]
[TD]51064[/TD]
[TD]R180-31[/TD]
[/TR]
[TR]
[TD]338[/TD]
[TD]676[/TD]
[TD]Professional Reforestation[/TD]
[TD]04/01/2019[/TD]
[TD]$1,881.23[/TD]
[TD]427405-36[/TD]
[TD]51064[/TD]
[TD]R180-32[/TD]
[/TR]
[TR]
[TD]339[/TD]
[TD]676[/TD]
[TD]Professional Reforestation[/TD]
[TD]04/16/2019[/TD]
[TD]$1,860.42[/TD]
[TD]427405-36[/TD]
[TD]51064[/TD]
[TD]R1840-34[/TD]
[/TR]
[TR]
[TD]340[/TD]
[TD]680[/TD]
[TD]Professional Reforestation[/TD]
[TD]07/02/2018[/TD]
[TD]$25,469.60[/TD]
[TD]427405-46[/TD]
[TD]51064[/TD]
[TD]R1734-33[/TD]
[/TR]
[TR]
[TD]341[/TD]
[TD]680[/TD]
[TD]Professional Reforestation[/TD]
[TD]07/10/2018[/TD]
[TD]$10,101.38[/TD]
[TD]427405-46[/TD]
[TD]51064[/TD]
[TD]R1734-34[/TD]
[/TR]
[TR]
[TD]342[/TD]
[TD]680[/TD]
[TD]Western Pacific[/TD]
[TD]07/17/2018[/TD]
[TD]$1,285.00[/TD]
[TD]427405-46[/TD]
[TD]51064[/TD]
[TD]2208[/TD]
[/TR]
[TR]
[TD]343[/TD]
[TD]681[/TD]
[TD]Professional Reforestation[/TD]
[TD]07/22/2018[/TD]
[TD]$382.50[/TD]
[TD]427405-46[/TD]
[TD]51064[/TD]
[TD]R1734-36[/TD]
[/TR]
</tbody>[/TABLE]
Lines 322-333 would copy over as:
Project 1 sheet:
[TABLE="width: 725"]
<tbody>[TR]
[TD] A
PROJECT [/TD]
[TD] B
CONTRACTOR [/TD]
[TD] C
INVOICE DATE[/TD]
[TD] D
INVOICE AMOUNT[/TD]
[TD] E
PO DATE[/TD]
[TD] F
AFP NBR[/TD]
[TD] G
PAYMENT AMOUNT[/TD]
[/TR]
[TR]
[TD]427405-16[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/05/2018[/TD]
[TD]4065.38[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]427405-16[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/10/2018[/TD]
[TD]10279.03[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]427405-16[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/10/2018[/TD]
[TD]10165.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]427405-16[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/17/2018[/TD]
[TD]$3,022.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]427405-16[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/17/2018[/TD]
[TD]$18,601.38[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]427405-16[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/25/2018[/TD]
[TD]$2,434.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]427405-36[/TD]
[TD]Western Pacific[/TD]
[TD]01/22/2019[/TD]
[TD]$8,175.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]427405-36[/TD]
[TD]Western Pacific[/TD]
[TD]01/30/2019[/TD]
[TD]$7,655.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]427405-36[/TD]
[TD]Western Pacific[/TD]
[TD]02/05/2019[/TD]
[TD]$5,954.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thank you in advance for any help you can give!
Sande
I know the title is very generic but what I'm trying to do is hard to explain in a short sentence!
I'm working with multiple workbooks that I'm trying to combine into one workbook with multiple sheets for 2 reasons: 1-it will eliminate some double entry the client is doing, and 2-I'm trying to get the data streamlined for use in Power BI. Therefore I'm hoping I can find a way to do this, but I'm not sure its possible. The first sheet (Billing Info) is a list of vendors and their invoice date, amount, project number, etc. The sheets after that are split up by project number (Project 1, Project 2, etc.) and show the PO's that were generated to match the invoices.
What I'd like to do is: anytime something is entered on the Billing Info sheet for Project 1 (or Project 2, etc.), copy data from columns B, C, D, and E (See Billing Info Sheet screenshot below) over to the Project 1 sheet (See Project 1 screenshot below - B into B, C into C, D into D, and E into A). BUT (And this is the tricky part) --- When bringing the data over to Project 1, I'd like to sum the amounts in Billing Info-Column D when the date in Billing Info-Column C (Invoice Date) are the same AND the number in Billing Info-Column A (Site) are the same.
For example:
Billing Info sheet:
[TABLE="width: 726"]
<tbody>[TR]
[TD]Line#[/TD]
[TD] A
SITE[/TD]
[TD] B
CONTRACTOR[/TD]
[TD] C
INVOICE DATE[/TD]
[TD] D
AMOUNT[/TD]
[TD] E
PROJECT[/TD]
[TD] F
PCA[/TD]
[TD] G
INVOICE[/TD]
[/TR]
[TR]
[TD]322[/TD]
[TD]703[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/05/2018[/TD]
[TD]$4,065.38[/TD]
[TD]427405-16[/TD]
[TD]51047[/TD]
[TD]R1840-02[/TD]
[/TR]
[TR]
[TD]323[/TD]
[TD]680[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/10/2018[/TD]
[TD]$10,279.03[/TD]
[TD]427405-16[/TD]
[TD]51047[/TD]
[TD]R1840-03[/TD]
[/TR]
[TR]
[TD]324[/TD]
[TD]703[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/10/2018[/TD]
[TD]$6,352.52[/TD]
[TD]427405-16[/TD]
[TD]51047[/TD]
[TD]R1840-03[/TD]
[/TR]
[TR]
[TD]325[/TD]
[TD]703[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/10/2018[/TD]
[TD]$3,813.38[/TD]
[TD]427405-16[/TD]
[TD]51047[/TD]
[TD]R1840-03[/TD]
[/TR]
[TR]
[TD]326[/TD]
[TD]703[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/17/2018[/TD]
[TD]$3,022.00[/TD]
[TD]427405-16[/TD]
[TD]51047[/TD]
[TD]R1840-04[/TD]
[/TR]
[TR]
[TD]327[/TD]
[TD]704[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/17/2018[/TD]
[TD]$18,601.38[/TD]
[TD]427405-16[/TD]
[TD]51047[/TD]
[TD]R1840-04[/TD]
[/TR]
[TR]
[TD]328[/TD]
[TD]680[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/25/2018[/TD]
[TD]$2,434.00[/TD]
[TD]427405-16[/TD]
[TD]51047[/TD]
[TD]R1840-05[/TD]
[/TR]
[TR]
[TD]329[/TD]
[TD]703[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/25/2018[/TD]
[TD]-$108.37[/TD]
[TD]427405-16[/TD]
[TD]51047[/TD]
[TD]R1840-05[/TD]
[/TR]
[TR]
[TD]330[/TD]
[TD]704[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/25/2018[/TD]
[TD]$27,277.51[/TD]
[TD]427405-16[/TD]
[TD]51047[/TD]
[TD]R1840-05[/TD]
[/TR]
[TR]
[TD]331[/TD]
[TD]676[/TD]
[TD]Western Pacific[/TD]
[TD]01/22/2019[/TD]
[TD]$8,175.50[/TD]
[TD]427405-36[/TD]
[TD]51064[/TD]
[TD]2236[/TD]
[/TR]
[TR]
[TD]332[/TD]
[TD]676[/TD]
[TD]Western Pacific[/TD]
[TD]01/30/2019[/TD]
[TD]$7,655.00[/TD]
[TD]427405-36[/TD]
[TD]51064[/TD]
[TD]2238[/TD]
[/TR]
[TR]
[TD]333[/TD]
[TD]676[/TD]
[TD]Western Pacific[/TD]
[TD]02/05/2019[/TD]
[TD]$5,954.50[/TD]
[TD]427405-36[/TD]
[TD]51064[/TD]
[TD]2239[/TD]
[/TR]
[TR]
[TD]334[/TD]
[TD]676[/TD]
[TD]Professional Reforestation[/TD]
[TD]02/19/2019[/TD]
[TD]$5,627.52[/TD]
[TD]427405-36[/TD]
[TD]51064[/TD]
[TD]R1840-26[/TD]
[/TR]
[TR]
[TD]335[/TD]
[TD]676[/TD]
[TD]Professional Reforestation[/TD]
[TD]03/06/2019[/TD]
[TD]$3,723.64[/TD]
[TD]427405-36[/TD]
[TD]51064[/TD]
[TD]R180-28[/TD]
[/TR]
[TR]
[TD]336[/TD]
[TD]676[/TD]
[TD]Professional Reforestation[/TD]
[TD]03/19/2019[/TD]
[TD]$2,524.88[/TD]
[TD]427405-36[/TD]
[TD]51064[/TD]
[TD]R180-29[/TD]
[/TR]
[TR]
[TD]337[/TD]
[TD]676[/TD]
[TD]Professional Reforestation[/TD]
[TD]03/26/2019[/TD]
[TD]$1,898.58[/TD]
[TD]427405-36[/TD]
[TD]51064[/TD]
[TD]R180-31[/TD]
[/TR]
[TR]
[TD]338[/TD]
[TD]676[/TD]
[TD]Professional Reforestation[/TD]
[TD]04/01/2019[/TD]
[TD]$1,881.23[/TD]
[TD]427405-36[/TD]
[TD]51064[/TD]
[TD]R180-32[/TD]
[/TR]
[TR]
[TD]339[/TD]
[TD]676[/TD]
[TD]Professional Reforestation[/TD]
[TD]04/16/2019[/TD]
[TD]$1,860.42[/TD]
[TD]427405-36[/TD]
[TD]51064[/TD]
[TD]R1840-34[/TD]
[/TR]
[TR]
[TD]340[/TD]
[TD]680[/TD]
[TD]Professional Reforestation[/TD]
[TD]07/02/2018[/TD]
[TD]$25,469.60[/TD]
[TD]427405-46[/TD]
[TD]51064[/TD]
[TD]R1734-33[/TD]
[/TR]
[TR]
[TD]341[/TD]
[TD]680[/TD]
[TD]Professional Reforestation[/TD]
[TD]07/10/2018[/TD]
[TD]$10,101.38[/TD]
[TD]427405-46[/TD]
[TD]51064[/TD]
[TD]R1734-34[/TD]
[/TR]
[TR]
[TD]342[/TD]
[TD]680[/TD]
[TD]Western Pacific[/TD]
[TD]07/17/2018[/TD]
[TD]$1,285.00[/TD]
[TD]427405-46[/TD]
[TD]51064[/TD]
[TD]2208[/TD]
[/TR]
[TR]
[TD]343[/TD]
[TD]681[/TD]
[TD]Professional Reforestation[/TD]
[TD]07/22/2018[/TD]
[TD]$382.50[/TD]
[TD]427405-46[/TD]
[TD]51064[/TD]
[TD]R1734-36[/TD]
[/TR]
</tbody>[/TABLE]
Lines 322-333 would copy over as:
Project 1 sheet:
[TABLE="width: 725"]
<tbody>[TR]
[TD] A
PROJECT [/TD]
[TD] B
CONTRACTOR [/TD]
[TD] C
INVOICE DATE[/TD]
[TD] D
INVOICE AMOUNT[/TD]
[TD] E
PO DATE[/TD]
[TD] F
AFP NBR[/TD]
[TD] G
PAYMENT AMOUNT[/TD]
[/TR]
[TR]
[TD]427405-16[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/05/2018[/TD]
[TD]4065.38[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]427405-16[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/10/2018[/TD]
[TD]10279.03[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]427405-16[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/10/2018[/TD]
[TD]10165.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]427405-16[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/17/2018[/TD]
[TD]$3,022.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]427405-16[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/17/2018[/TD]
[TD]$18,601.38[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]427405-16[/TD]
[TD]Professional Reforestation[/TD]
[TD]09/25/2018[/TD]
[TD]$2,434.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]427405-36[/TD]
[TD]Western Pacific[/TD]
[TD]01/22/2019[/TD]
[TD]$8,175.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]427405-36[/TD]
[TD]Western Pacific[/TD]
[TD]01/30/2019[/TD]
[TD]$7,655.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]427405-36[/TD]
[TD]Western Pacific[/TD]
[TD]02/05/2019[/TD]
[TD]$5,954.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thank you in advance for any help you can give!
Sande
