Issues with Creating a Pivot Table from multiple worksheets of a workbook

Question

This is a follow up to this question: Pivot Table from multiple Data Tables containing same row headers, different data

Answered with this video: http://www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-the-same-workbook/

Thank you so much, Ashish Mathur for the amazing solution! I am so glad MS Query exists!

Some pitfallsI ran into (written here for benefit of others):

1. You cannot use Table names. You must create a name for the range.
2. Adding columns outside the range - must redefine the range name to include new columns.

Some remaining issues:

1. I renamed some tabs before and then created the MS Query Pivot Table. Now when I "Refresh All" for that Pivot Table, it gives an error: "Cannot open PivotTable source file '...'." Giving the name of the tab before it was renamed. So refreshing "ALL" of one Pivot Table refreshes all other Pivot Tables too?

2. Querying too many tables (like in your video, if you were to query all 12 months), gives an error of too many fields. 

Solutions? Maybe query 6 months at a time, to create semi-annual tables, and then query those semiannual tables to create a longer history Pivot Table?

3. Adding a table to an existing query.

I clicked on Existing Connections, renamed the connection names, since I made several, then right clicked on one to get to "Connection Properties" window, in which under the Definition Tab I clicked "Edit Query..." (hoping to add more tables). This resulted in an error titled "ODBC Excel Driver Login Failed" with the message: "Unrecognized database format '...'" giving the address of the Excel Workbook. (By the way, I am querying only from tabs in the same workbook as the destination)

Clicking OK, brings up the "Select Workbook" dialog, from which, if I select the usual Excel Table (just like in the beginning), the same "Unrecognized database format" error pops up.

Workarounds? Every new month (additional table) the table must be rebuilt from the beginning...

I'll stop with the issues for now. Should I start a separate question in this forum for each issue or is this format ok?

Thank you so much for your help!

Answer 1

Hi,

You are most welcome.  I accept the two pitfalls.  I have not tried the remaining issues but I am sure that you have tried them and have run into those specific issues.

As Herbert has pointed out, the best solution here would be to combine data from multiple worksheets into a single worksheet using Power Query and then create a Pivot Table.

Answer 2

Easier with free Excel 2010 Power Query and PowerPivot Add-Ins.

Share file(s).