With Power Query in Excel 365, you can do just that. This powerful tool allows you to merge files from a folder into a single dataset, automatically updating as new files are added.

Power Query is specifically designed to handle complex data consolidation tasks, even when working with files that have varying structures or inconsistent sheet names.