Hi, Can someone help me with VBA code that does the below.
I have two sheets where one is source and another is output. I will update source sheet weekly and VBA should update output.
Source sheet looks as below. it will have both current week's and past weeks data.
my output sheet looks as below. below steps needs to be done.
1. Find the Report date from the cell Y2 (10/17/20) and needs to find out where is that date in first row (In this case it is "D" row. this is where values to be entered)
2. now pick a name from column A from output sheet and look for the same in either column U or V in source sheet and then filter the report date in column H of source sheet and sum of respective sales amount to be published in cells of "D" row.
I have posted a similar query before but it was bit complicated and haven't recived any responses. this time made my requirement simpler. Thnak you in adavance.
I have two sheets where one is source and another is output. I will update source sheet weekly and VBA should update output.
Source sheet looks as below. it will have both current week's and past weeks data.
Column H | Column J | Column U | Column V |
---|---|---|---|
WEEK_END_DATE | Sales | Senior Manager | Manager |
10/17/2020 | 40 | Hari | Max |
10/17/2020 | 30 | Sam | James |
10/17/2020 | 60 | Hari | Max |
10/17/2020 | 10 | Hari | Colin |
10/17/2020 | 20 | Hari | Jade |
10/17/2020 | 40 | Sam | Varun |
10/17/2020 | 50 | Sam | James |
10/17/2020 | 70 | Sam | Murat |
10/10/2020 | 70 | Sam | Murat |
my output sheet looks as below. below steps needs to be done.
1. Find the Report date from the cell Y2 (10/17/20) and needs to find out where is that date in first row (In this case it is "D" row. this is where values to be entered)
2. now pick a name from column A from output sheet and look for the same in either column U or V in source sheet and then filter the report date in column H of source sheet and sum of respective sales amount to be published in cells of "D" row.
Column A | Column B | Column C | Column D | Column Y | |
---|---|---|---|---|---|
Senior Manager/Manager | 10/3/2020 | 10/10/2020 | 10/17/2020 | | Report date |
Hari | 80 | 60 | 130 | | 10/17/2020 |
Max | 40 | 40 | 100 | | |
Colin | 40 | 20 | 10 | | |
Jade | | | 20 | | |
Sam | 90 | 80 | 190 | | |
Varun | 20 | 30 | 40 | | |
James | 20 | 20 | 80 | | |
Murat | 30 | 20 | 70 | | |
Jade | 20 | 10 |
I have posted a similar query before but it was bit complicated and haven't recived any responses. this time made my requirement simpler. Thnak you in adavance.