Hi Dwight,

Welcome to our forum! You can and should use Power Query for this. See this tutorial: consolidate Excel sheets with Power Query.

Mynda

Good morning Mynda,

Thank you for the response.

I don't have the same toolbar as you in my Office 2016:
(FYI I am working on a corporate machine)>

I've attached screenshots for you to see:]

SO I was able to figure out the differences between our versions of Excel and try using Power Query.

When I use the "=Excel.CurrentWorkbook ()" formula I get errors, see below:

Hard to tell much from that screenshot. If you click on the Error cell it will show you an error message with further information. 

That said, it looks like there's a PivotTable behind the query editor. You can't connect to a PivotTable. Better to connect to the PivotTable's source data.

If that's not the problem, then try removing the space after the function name and before the opening parentheses. There shouldn't be spaces there.

Mynda

Load (attach) some sample data  that is representative (8-10 records per sheet/workbook) of your actual data and we can then demonstrate.  NO Pictures.  Cannot manipulate data in pictures.

Hi Mynda, 
Yes there are pivot tables, but I'm not pulling the data from the pivot tables, but the actual data sheets that the pivot tables connect to. 

FYI
Here is some sample Data from my tables. 

Hi Dwight,

Thanks for sharing the file. The only problem I see with that data is it isn't formatted in an Excel Table or defined by a name, as required by Power Query to consolidate data from multiple sheets in the current file.

Follow the Power Query tutorial linked to above and see file attached.

Mynda

Hi Mynda,

What do you mean they're not Excel Tables??

They're tables that were created and formatted in Excel

They're not formatted as an Excel Table. Follow the link in my post above for Excel Table and you'll see what I mean. If you also open the file I shared with you, you'll see their format.

Mynda

Thank you Mynda, 
I got them formatted properly and was able to run the query.

I also figured out how to get the query to give me totals by date which helps me. 

Now I  can do a total for each month and add that to my data set for my boss to compare 🙂 

Hey Mynda, 
One question I did have though:
How do I add additional tables to my Power Query??

Can I just edit the query and add additional tables as I create them.

Or will I need to create a new query every month when I create a new table for that month's data?

Thanks 

IKE

If you don't apply any filters at Step 4 in the Consolidate Sheets with Power Query tutorial, then any new tables will be automatically included.

That said, you should go back into the query after 'closing & loading' and filter out the table that contains the final query , assuming you chose to 'close & load' to a Table. Otherwise, your query table will be double counted.

DISREGARD!!!
I figured out what happened.
Somehow all was unchecked for the Document ID indicator and that was preventing it from pulling all the data 🙂 DUH!!!

Hey Mynda, 
Would changing the table to an "Excel Table" keep my Pivot table pulling from the Data table from updating Properly?

I have a total errors section that is not showing the current total errors based on what's on the Data Sheet. 

I have included 2 screenshots:
1 - The raw data in the "excel table" showing total # of errors

2 - Screenshot of my Pivot Table showing the sum of errors pulled from the Data Sheet.

I have refreshed all and ensured my source data encompasses all of the data on the Spreadsheet.

Thanks