Pivot Table data sourcing query

[Charmwah]

Hi all

I have a pivot table (on its own worksheet) which displays monthly figures, of which the data source is a single range in another worksheet within the same workbook. Currently I have a separate workbook (and thus a separate pivot table) for each month. What i'd like to do is combine all the monthly worksheets into a single workbook, and then have a single dynamic pivot table which will show the monthly figures.

I had thought it might be possible to do this by selecting the month from a drop down list or radio button, which then links to the data source for that particular month, but i'm not so sure its is possible?

I should also add that my pivot table contains some additional calculated fields where monthly figures need to have a formula to give them a weighted value.

Thanks in advance!

Dave

 

[istiasztalos]

Hi !

There's a less known neat trick you could use here... Excel allows a named range to be defined by an OFFSET formula with dynamic placement, length & width!

You can name a cell for example 'sheetforpivot' that's the name of the sheet you want to be the source of your pivot table

Then go to Name Manager and create a named range - for example 'mypivotsource' - where this is the definition
(depending on where is your data on the target sheet, adjust the offset base point accordingly, those are the two 0s, and the COUNTA functions, which adjust for the length and width of the data)

=OFFSET(INDIRECT(sheetforpivot&"!A1"),0,0,COUNTA(INDIRECT(sheetforpivot&"!A:A")),COUNTA(INDIRECT(sheetforpivot&"!1:1")))

Then make the source of your pivot the named range 'mypivotsource'

 

[SpillerBD]

What i'd like to do is combine all the monthly worksheets into a single workbook, and then have a single dynamic pivot table which will show the monthly figures....
Dave

If you have Excel 2016 (or Excel 2013 ProPlus) you should use Get & Transform to aggregate your data into a single Table or Data Model on which you can then perform a Pivot Table. You will probably want to use a Table as it provides the familiar experience and not the pro/con of the Data Model.

You would then be able to run any date/data range comparisons. Additional row based calculations can be done in the Query process.

 

[Charmwah]

If you have Excel 2016 (or Excel 2013 ProPlus) you should use Get & Transform to aggregate your data into a single Table or Data Model on which you can then perform a Pivot Table. You will probably want to use a Table as it provides the familiar experience and not the pro/con of the Data Model.

You would then be able to run any date/data range comparisons. Additional row based calculations can be done in the Query process.

Hi

I tried this 'Get & Transform' and I can easily pull in the multiple pivot tables into a single worksheet, however combining them doesn't work so well :/