Sonythenovice
New Member
- Joined
- Mar 15, 2016
- Messages
- 31
Hello,
A big thank you to everyone who has helped me already today, one more question if thats ok?
I have a Sheet called "Data" that hold all the sales for each salesperson on a daily basis by date,
I want a macro that can add up the sales each person has done for the week from a date i put into a cell.
So to describe my data sheet "Data"
B8:ABE8 are the dates entered as DD/MM/YYYY
B11:ABE31 is all the sales made by each salesperson,
for example Tomas is B11:ABE11
and Susan is B12:ABE12
the sales person names will always be in the same rows so it ok to assume that if Tomas is in Row 11 all the data in row 11 is for him.
I also have another tab called "Reports"
In reports down column A I have all the sales persons names starting at A11 (the names are in the same order as the "Data" tab so you can assume A11 will be Tomas, A12 Susan etc.
in Cell B8 I input a date for example 21/08/2015
I would like a macro that could look along Sheet "Data" Row 8 and Find the date that matches "Reports" B8's date, then add up each row's sales for the last 7 days including that day and insert it into Reports Column B from B11:B31
if anyone has any ideas how i can do this i'd be very greatful
Thanks
Simple Example of what I need using a few dates and a few names
Sheet DATA:
<tbody>
</tbody>
Sheet Result:
<tbody>
</tbody>
A big thank you to everyone who has helped me already today, one more question if thats ok?
I have a Sheet called "Data" that hold all the sales for each salesperson on a daily basis by date,
I want a macro that can add up the sales each person has done for the week from a date i put into a cell.
So to describe my data sheet "Data"
B8:ABE8 are the dates entered as DD/MM/YYYY
B11:ABE31 is all the sales made by each salesperson,
for example Tomas is B11:ABE11
and Susan is B12:ABE12
the sales person names will always be in the same rows so it ok to assume that if Tomas is in Row 11 all the data in row 11 is for him.
I also have another tab called "Reports"
In reports down column A I have all the sales persons names starting at A11 (the names are in the same order as the "Data" tab so you can assume A11 will be Tomas, A12 Susan etc.
in Cell B8 I input a date for example 21/08/2015
I would like a macro that could look along Sheet "Data" Row 8 and Find the date that matches "Reports" B8's date, then add up each row's sales for the last 7 days including that day and insert it into Reports Column B from B11:B31
if anyone has any ideas how i can do this i'd be very greatful
Thanks
Simple Example of what I need using a few dates and a few names
Sheet DATA:
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | |
8 | Date | 12/05/2015 | 13/05/2015 | 14/05/2015 | 15/05/2015 | 16/05/2015 | 17/05/2015 | 18/05/2015 | 19/05/2015 | 20/05/2015 | 21/05/2015 | 22/05/2015 | 23/05/2015 | 24/05/2015 | 25/05/2015 | 26/05/2015 | 27/05/2015 | 28/05/2015 | ||||||
SALESPERSON | when it finds the date it will look back 7 days then add the amount for each salesperson and put the total into results sheet column B | |||||||||||||||||||||||
11 | Tomas | 10 | 5 | 33 | 55 | 9 | 999 | 5 | 6 | 101 | 100 | 2080 | 0 | 0 | 0 | 12 | 11 | |||||||
12 | Susan | 123 | 22 | 44 | 65 | 10 | 20 | 10 | 20 | 10 | 20 | 10 | 20 | 10 | 5 | 22 | 0 | 7 | ||||||
13 | John | 0 | 0 | 0 | 0 | 0 | 23 | 27 | 50 | 100 | 200 | 300 | 100 | 200 | 300 | 0 | 121 | 2345 |
<tbody>
</tbody>
Sheet Result:
A | B | C | D | E | F | G | H | I | |
8 | 23/05/2015 | so the macro will take the date from B8 and find it in the data sheet from row 8 | |||||||
Totals Sales For Week | |||||||||
11 | Tomas | 3290 | |||||||
12 | Susan | 110 | |||||||
13 | John | 800 | |||||||
<tbody>
</tbody>