Need pivot table to summarize multiple tables

I have several sets of data organized into tables.  Each table contains the same headings.  The tables contain detail about projects related to facilities, and I need to use  a pivot table to summarize the data.  I need to end up with a pivot table where the rows are building names and the columns are years, and the values are sums of all of the projects for that building in that year.  I've used the pivot table wizard and the multiple consolidation ranges, but I don't end up with the columns in my original tables as choices for my pivot table.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
what version of Excel do you have>?
pjfromnyAuthor Commented:
Office 365
ProfessorJimJamMicrosoft Excel ExpertCommented:
well, then very easy way.

Use the Data Model to create a new PivotTable

Click any cell on the worksheet.

Click Insert > PivotTable.

PivotTable button on the Insert tab

In the Create PivotTable dialog box, under Choose the data that you want to analyze, click Use an external data source.

Create PivotTable dialog box with Use an external data source selected

Click Choose Connection.

On the Tables tab, in This Workbook Data Model, select Tables in Workbook Data Model.
Tables in the Data Model

Click Open, and then click OK to show a Field List containing all the tables in the Data Model.
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

ProfessorJimJamMicrosoft Excel ExpertCommented:
also another way,  using this video tutorial that shows how to do it without use of external link as i mentioned above.

with this you simply add it to the data module
ProfessorJimJamMicrosoft Excel ExpertCommented:
pjfromnyAuthor Commented:
I don't see the screen as in the last graphic.  When I choose "tables,"   I see the following:

Choose connection screen
Those are two files that I have open, though the tables with contain the data are not specified, just the range the table covers.  I can choose one but not both.  I don't see anything that looks like "4 Tables in workbook table model."

The tables are in separate excel files.
ProfessorJimJamMicrosoft Excel ExpertCommented:
you were clear in your question, as you did not mention that your tables are in separate workbooks.

so, you have multiple options to get this sorted

A)  using Microsoft Power Query free Add-in    you need to instal the 32 bit if you are using 32 bit of Office   .  then using the same query how to first append your data into one see this    also step by step guide on how to create pivot table from multiple workbook using power query .

B)  you have the option of VBA that prompts for the files to select and then using Select UNION, it automatically creates the pivot from the selected workbooks.

let me know how it goes

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pjfromnyAuthor Commented:
It worked!  A few followup questions:
 - it seems to only work with csv files; is that true?  I tried with .xlsx files but it returned an error.
- if additional data is added, is that brought into the aggregated file if the query is refreshed?
-is it possible to add new files to the query?

Thank you.
ProfessorJimJamMicrosoft Excel ExpertCommented:
What option did you use? Power query or vba?
What is the error number or description?

If you used the vba option then I can change the code that can work in xlsx files.

With simple refresh of pivot table the additional data cannot be aggregated then vba need to run to do that.
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.