Forum Discussion
Pull data from multiple files
I'm going to set your initial question aside for the moment. If somebody else answers it and tells you how, you're free to ignore my question and follow their advice..... but I want to begin by asking you first why you have two dozen workbooks each with six tabs or sheets, all with the same format?! That's 144 sets of data, which now, it would appear, you're wanting to consolidate and analyze.
My guess is that you (or somebody else, a colleague perhaps) created these because initially it made sense to keep separate the data being tracked on 24 different (stores, people, projects, .... whatevers) and the six sheets within each workbook similarly represent six different subsets within the primary entity or time period or....(whatever, again).
It made sense because that's how we would have done it were we tracking all of these bits of data on paper ledger sheets. And it was cleaner/clearer to replicate that sort of organizational paradigm within Excel.
The fact of the matter is, had you collected all that data in one huge dataset, it would be far cleaner and easier now to summarize it, to slice and dice it, to calculate averages, totals, medians...(whatever, again). Excel has remarkable tools to break apart well designed large databases.
Which leads me to suggest that rather than now figuring out how to extract data-element-by-data-element from each of those 144 sheets so that you can do whatever comprehensive analyses you are hoping to do...rather than doing that, you start with the (probably) fairly straight-forward work of consolidating all that data into a single data table, after which your comprehensive analyses will be easier and more reliable.
Yes, it will take a little bit of time to accomplish that consolidation. Depending on how those 144 sheets are organized, it could in fact be quick work. But that depends, as I said, on how they're organized.
Is it possible for you to post a sample? An actual workbook would be ideal, any one of the 24 workbooks. If they contain confidential information, however, you'll need to render anonymous whatever is necessary. If that's too complicated, an image that gives an idea of how these sheets are organized, just blotting out the confidential components. Actual files can be posted on OneDrive or GoogleDrive, with a link pasted here that grants access to the file. Images can be pasted directly here in one of these message windows.