@LEE_STEWART
Let me offer a bit more of an explanation to my last post. I know it sounds like, potentially, a lot of work to now consolidate weeks or days worth of separate spreadsheets into a single database. So let me offer a bit of my own reasoning. I have come to this from a career (retired now) that included not only a lot of spreadsheets but also a role as director of a major corporation's HR and Payroll database. I have quite a bit of experience taking separate data tables and combining them to produce useful reports. I also have experience taking single tables in Excel and extracting data to produce useful summary reports.
Excel is a very powerful tool for analyzing data. Unfortunately, it is quite common for people to do what you've done here, which is to start at the lower level with daily or weekly sheets that summarize sales/orders/purchases/production at that daily or weekly level. Often these sheets are essentially little more than automated copies of the kinds of records that previously were kept in paper ledger sheets. It is true that Excel can work to pull together multiple sheets into a single report. You were starting with one method of doing that. Reference has also been made to Power Query, which is, as the label implies, a powerful way to query multiple tables, joining them so as to produce useful reports. It's quite comparable to what I used to do with the HR/Payroll database at my company, where SQL (Structured Query Language) enabled us to produce reports that joined tens of separate data tables into a single "virtual table" for a variety of purposes.
Excel can do that.
Excel also has a number of tools that can take a single data table, a table that consolidates years worth of daily data, say, and extract and summarize very focused subsets of the whole. I've already mentioned the Pivot Table, which many find to be adequate for most such analyses. There are also both menu items and, recently a function for FILTERing or SORTing selected data. Coupled with other functions such as MIN and MAX, AVERAGE, etc., etc., there truly is a wide array of summary reports that are readily produced from a single database that contains all the micro-level ("highly granular" is the way a lot of data geeks refer to it) details.
And the reality is that it's easier and more flexible to use Excel's abilities to focus and extract, to filter, to cross-tabulate, from a single database, than it is to take a host of separate spreadsheets and combine them (via Power Query) so as to do that same cross-tabulation. It's one thing if all you're combining are, say, three or four separate workbooks, but when your source data for a year has been captured in 52 separate workbooks, then it becomes quite unwieldly. Possible, but unwieldly.
