1

I thought there would be a simple way of doing this, but unfortunately I have not come across one. My company has an Excel workbook with 12 sheets (1 for each month), into which I enter sales data as accounts are written. I reformatted each month's data into tables, thinking that this would provide an easy reference to gather the data into a pivot table that joins all the months and would be updated as I enter data; however, a pivot table based on multiple sets of data allows highly limited manipulation.

So what I want to do is create a new table that is automatically populated as I enter data in any of the 12 current tables, to combine them into a master listing. I have tried doing a query, but when I try to set up the data sources, it doesn't recognize my tables. I tried Power Query, but I couldn't get it to update the data as I updated the source. Consolidate also was not a useful feature, as it required all the data to be somehow calculated, and my columns need to simply be copied over, not summed or averaged.

As you can probably tell from my explanations and terminology, I'm no Excel expert. I don't know what VBA even is, let alone know how to use it, but I've seen it mentioned a lot, so I figure at some point in my life I should learn it.

Is there a formula or some other Excel 2010 feature that can automatically copy all of this data onto one running list, and keep it updating as I enter data in the source tables? It would have to run automatically.

Jkoug
  • 11
  • 2

1 Answers1

0

I believe your end goal is to have a pivot table which consolidates data from each of the individual 12 sheets/tables and not really to have the intermediate "single running list which is an aggregation of all the 12 sheets".

If so, I suggest to create an Excel Pivot table directly based upon the 'Multiple consolidation ranges'.

To start, create a new spreadsheet and select a cell (say A3) and use the click sequence Alt+D+P, this will bring up the PivotTable and PivotChart Wizard, and proceed further using the third option - 'Mulitple consolidation ranges'.

I will have to refer you to the below site for a detailed step by step instructions on the above: http://www.contextures.com/xlPivot08.html

Please be aware that the Difficulty level for this solution is Medium, suggest you to bookmark the solution from maintainability reasons, in case you choose to implement it.

Pang
  • 9,564
  • 146
  • 81
  • 122
Bharat Anand
  • 464
  • 1
  • 6
  • 14
  • do you miss this part of the OP - *however, a pivot table based on multiple sets of data allows highly limited manipulation.* – Scott Holtzman Jul 13 '16 at 02:41
  • I did, apologies, do I need to delete my answer as it is not relevant in this case :-( – Bharat Anand Jul 13 '16 at 03:03
  • Thank you for your response! Yes, I came across those instructions in my initial searching, and you just can't run a fully functional pivot table from multiple tables. That was why I wanted to instead pull all the tables into a master one, and then create a pivot table from that. I appreciate your time! – Jkoug Jul 13 '16 at 15:53