PeregrinTook
Board Regular
- Joined
- Feb 9, 2006
- Messages
- 157
Here's one for you creative formulae-type people!
I have a table of 10 rows for salespeople by 53 rows for each week-end date (always a Friday, header is format 29/09/2006) of the calendar year.
At the end of the table, after the last column, I would like 3 columns called "Last Week", "Month to Last Week" and "Rolling Quarter to Last Week"
The data will always be updated on a Monday, so I would like these new columns to update immediately, and preferably with formulae rather than vba if possible...
So at any given time:
"Last Week" should show what the values were for each salesperson in the last calendar week. So if opened today, "Last Week" should show values from w/e Friday 29th Sept.
"Month to Last Week" should show the sum of each person's sales for all weeks in the month of last week's update. So if opened today, even although we're now in October, "Month to Date" should show the sum of values from w/e Friday 1st Sept to w/e Friday 29th Sept because the last week of data is still September.
"Rolling Quarter to Last Week" should show the sum of each person's sales for the thirteen weeks up to and including last week. So if opened today, "Rolling Quarter to Last Week" should show the sum of values from w/e Friday 7th July to w/e Friday 29th Sept.
Hope all that makes sense, any help gratefully received!
Thanks
PeregrinTook
I have a table of 10 rows for salespeople by 53 rows for each week-end date (always a Friday, header is format 29/09/2006) of the calendar year.
At the end of the table, after the last column, I would like 3 columns called "Last Week", "Month to Last Week" and "Rolling Quarter to Last Week"
The data will always be updated on a Monday, so I would like these new columns to update immediately, and preferably with formulae rather than vba if possible...
So at any given time:
"Last Week" should show what the values were for each salesperson in the last calendar week. So if opened today, "Last Week" should show values from w/e Friday 29th Sept.
"Month to Last Week" should show the sum of each person's sales for all weeks in the month of last week's update. So if opened today, even although we're now in October, "Month to Date" should show the sum of values from w/e Friday 1st Sept to w/e Friday 29th Sept because the last week of data is still September.
"Rolling Quarter to Last Week" should show the sum of each person's sales for the thirteen weeks up to and including last week. So if opened today, "Rolling Quarter to Last Week" should show the sum of values from w/e Friday 7th July to w/e Friday 29th Sept.
Hope all that makes sense, any help gratefully received!
Thanks
PeregrinTook