Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possible getpivotdata with offset?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possible getpivotdata with offset?
This does look a tad 'over-complicated'. Take a look at this:
http://www.contextures.com/xlPivot06.html =getpivotdata() That function should give you what you want. Regards, Ryan--- -- RyGuy "Sarah (OGI)" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
GETPIVOTDATA = #N/A | Excel Discussion (Misc queries) | |||
GETPIVOTDATA | Excel Discussion (Misc queries) | |||
GETPIVOTDATA | Excel Discussion (Misc queries) | |||
GetPivotData ??? | Excel Worksheet Functions |