S
Sarah (OGI)
One worksheet is labelled 'Club'.
Data is entered from column B of row 6 - the layout of the data is displayed
below:
Col B / Col C / Col D / Col E / Col F / Col G / Col H / Col I / Col J / Col
K / Col L / Col M / Col N / Col O / Col P / Col Q / Col R
Client Ref / Client Name / Post Code / Live Date / Starting Total / Rolling
12 Month 1 / Rolling 12 Month 2......thru to.....Rolling 12 Month 12
1234 / A B C Ltd / BD1 1XX / 01/05/2008 / £12966 / £12558.....
The rows in between, i.e. row 7, row 9, row 11, etc, show date ranges from
column F onwards, i.e. starting directly underneath the Starting Total
column, then through to Rolling 12 Month 12. The date range in each of these
cells extends for 12 months, shifting by one month each time. For example:
In column F (under Starting Total and using a Live Date of 01/05/2008), the
date range shown would be: 01/05/2007 - 30/04/2008
In column G (under Rolling 12 Month 1), the date range shown would be:
01/06/2007 - 31/05/2008
The Starting Total and subsequent Rolling 12 figures derive from a pivot
table on another worksheet, labelled 'Pivot'.
The Pivot table shows Client Ref's down column A, dates along row 4
(displayed in the format "mmm-yy") and premium figures in the main data area.
Is there any way (via the getpivotdata function, I guess?) to extract the
sum of the fields for each client ref, for each of the specified date ranges.
For example, for a client ref of 1234 who has a live date of 01/05/2008, I
would want to pull out the total amount for the months May 07 through to Apr
08.
The following month, I would then need to pull out the same data, but
shifting the range by 1 month.
I'm thinking an OFFSET might be needed, but I'm not sure how this works.
(Apologies if I've over-complicated the problem).
Any help would be greatly appreciated!
Many thanks in advance.
Data is entered from column B of row 6 - the layout of the data is displayed
below:
Col B / Col C / Col D / Col E / Col F / Col G / Col H / Col I / Col J / Col
K / Col L / Col M / Col N / Col O / Col P / Col Q / Col R
Client Ref / Client Name / Post Code / Live Date / Starting Total / Rolling
12 Month 1 / Rolling 12 Month 2......thru to.....Rolling 12 Month 12
1234 / A B C Ltd / BD1 1XX / 01/05/2008 / £12966 / £12558.....
The rows in between, i.e. row 7, row 9, row 11, etc, show date ranges from
column F onwards, i.e. starting directly underneath the Starting Total
column, then through to Rolling 12 Month 12. The date range in each of these
cells extends for 12 months, shifting by one month each time. For example:
In column F (under Starting Total and using a Live Date of 01/05/2008), the
date range shown would be: 01/05/2007 - 30/04/2008
In column G (under Rolling 12 Month 1), the date range shown would be:
01/06/2007 - 31/05/2008
The Starting Total and subsequent Rolling 12 figures derive from a pivot
table on another worksheet, labelled 'Pivot'.
The Pivot table shows Client Ref's down column A, dates along row 4
(displayed in the format "mmm-yy") and premium figures in the main data area.
Is there any way (via the getpivotdata function, I guess?) to extract the
sum of the fields for each client ref, for each of the specified date ranges.
For example, for a client ref of 1234 who has a live date of 01/05/2008, I
would want to pull out the total amount for the months May 07 through to Apr
08.
The following month, I would then need to pull out the same data, but
shifting the range by 1 month.
I'm thinking an OFFSET might be needed, but I'm not sure how this works.
(Apologies if I've over-complicated the problem).
Any help would be greatly appreciated!
Many thanks in advance.