Consolidating data multiple excel files
You can also enable Create links to source data option, for linking the source data(this option is applied to new workbook ), see screenshot: 4. All the data from the worksheets has been added-up into a new worksheet.At the same time, it creates links for the consolidated data, which updates automatically when the source data changes.While each file is structured the same, the information within sheet 1 and 2 (for example) is different.
Since you wanted them on one sheet, I think the above isn't what you were looking for. Especially, if different sheets have a similar identifier (part number/contact person/ect).If you are not familiar with the Consolidate function in Excel, here I will recommend you an easy and multi-functional tool-Kutools for Excel.With its Combine feature, you can quickly summarize the data from multiple worksheets or workbooks into one master worksheet.Kutools for Excel with more than 120 handy Excel add-ins, free to try with no limitation in 30 days. After installing Kutools for Excel, please click Enterprise Combine. In the Combine Worksheets wizard, select Consolidate and calculate values across multiple workbooks into one worksheet. On step2 of wizard, all the opened workbooks and worksheets are listed in the list boxes, you can click Add button to add other files or folders that you want to consolidate.Click button to select the range you want to summarize one by one.