Consolidating multiple excel files into one dating a capricorn woman
For example One.xlsx, Two.xlsx, Three.xlsx, each contain information on their respective Sheet1.
I need the information on Sheet1 from each workbook to be combined into a single workbook with sheets that are named from the file name of the original workbook.
However the tabs are not named from the name of the original file. In this case a coworker was able to get me started and I further modified it to meet my needs.
For now all the underlying Workbooks will be in the same folder.
The Consolidate tool allows you to combine worksheets where data is defined by position or by category. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Condensing Multiple Worksheets Into One.
Alternatively, you can use Power Query in Excel 2013, renamed Get and Transform in Excel 2016, but those features require far more experience with database queries than the average Excel user has accumulated at this point.
Fortunately, Microsoft Query is an alternative that is available in all versions of Excel that can guide you through the process.
Note: if you don't check Top row and Left column, Excel sums all cells that have the same position.
Click in the Reference box, select the range A1: E4 in the district1 workbook, and click Add. Repeat step 4 for the district2 and district3 workbook. Check Top row, Left column and Create links to source data.