Pivot table using data that has run onto a second sheet

[ktharmer]

Hello,

I'm using Excel 2013. I have a sheet of data (Sheet1) with columns A to K containing data. Each month I add more rows to Sheet1 (the columns remain the same).

I have a pivot table with data source 'Sheet1'!$A:$K which I refresh each month after additional data rows have been added into Sheet1.

I have now filled all the available rows in Sheet 1 but need to continue adding rows to the data which feeds into the pivot table. I can either continue adding rows (with the same column headers) on Sheet2 (preferably), or on Sheet1 columns M:W.

How can I add these additional rows of data into the same pivot table data range? Ideally without using VB.

Thanks in advance.

K

[sandy666]

change your source range to Excel Table, recreate Pivot Table where you want
after that you can add more rows in source and refresh PivotTable. your data will be visible in PT automatically

[CK76]

Alternately you can use Pivot Table wizard to consolidate multiple ranges.

Assuming structure of the data remains same...

ALT + D then P to launch PivotTable wizard.

Select "Multiple consolidation ranges". Hit Next.

Unless you need multiple page filter, just have it set to "Create a single page field for me". Hit Next.

Select A:K in Sheet1 and add. Then select same range in Sheet2 and add. Hit Next. (or convert both to table and add tables).

Create pivot where you want it.

Now, if you add to sheet2 data, when you refresh pivot, it will be there.

[ktharmer]

Alternately you can use Pivot Table wizard to consolidate multiple ranges.

Assuming structure of the data remains same...

ALT + D then P to launch PivotTable wizard.

Select "Multiple consolidation ranges". Hit Next.

Unless you need multiple page filter, just have it set to "Create a single page field for me". Hit Next.

Select A:K in Sheet1 and add. Then select same range in Sheet2 and add. Hit Next. (or convert both to table and add tables).

Create pivot where you want it.

Now, if you add to sheet2 data, when you refresh pivot, it will be there.

Thanks, however when I create the pivot in this way, on the Pivot Table Fields box to the right of the page, in the "Choose fields to add to report" section, it just shows "Row", "Column", "Value", "Page1", whereas before, this section showed all the headers of columns A:K so that I could drag these and create a pivot as appropriate. I can't see how I can replicate the same pivot table with just "Row", "Column", "Value", "Page1" to pick from.

Help appreciated. Thanks

[CK76]

You can also leverage MS Query in conjunction with PivotTable Wizard to Union two tables. Or if you have access to it, PowerQuery can do the same without need for PivotWizard.

Alternately, you can create list of unique key in 3rd table and use data relationship and use that to create pivot.

If you can upload a sample file. I can help you create steps for one of above that suites your need.

[ktharmer]

If you can upload a sample file. I can help you create steps for one of above that suites your need.

Please see attached containing 2 sheets of data and a pivot table which pulls from Data1 tab. The data I'm actually using has utilised all rows on Data1 tab, hence the need to run onto Data2 tab.

Thanks

[sandy666]

PivotTable from both tables Data1 & Data2 via PowerQuery (PowerQuery - free add-in for Excel 2013 from MS site)

is that what you want?

[CK76]

@ktharmer

sandy666's method is recommended and preferred.

If for some reason or another you cannot use PQ. Then your next best option is to use MS Query.

However, MS query has issue with data stored on same workbook, you will need data workbook separate from Pivot Workbook.
As well, there's no way to make folder path dynamic without bit of VBA to my knowledge.

If you need further help on MS Query method, let me know.

[ktharmer]

Thanks, all, for your suggestions. Unfortunately I cannot install PQ on my work laptop.

It's looking like VB is the best alternative. Do you have any code which would work? (assuming I am a novice with VB...)

Continued thanks!

[CK76]

Sure. Try this. But don't know how efficient it will be with your actual data. As it combines both sheets in memory using array object.
Then loads PivotCache using the array.

You will need to add "Microsoft ActiveX Data Objects Recordset x.x Library" as reference in Project.

Function to combine array is from stackoverflow I believe, but can't remember exactly where I got it from (it's been couple of years).

Please Login or Register  to view this content.

Note: This will create pivot table in a new sheet with no fields added in with no formatting. You'll need to add them.
0.JPG

Test and let me know how it works out. This is just a demo, so I haven't put in routine to delete pivot's when new one is created. So manually delete generated pivot before you run the code for the second time for now.