Bring together tons of spreadsheets?
Alright, I don't know where to ask this so I'm coming to you all. Feel free to point me elsewhere.
I have a task, there are Microsoft Excel spreadsheets, one for each day of the year going back into 2005. Each workbook has one worksheet in it with between 200 and 1000 lines. Each line represents one transaction. One or my coworkers goes through each day and marks whether the transaction is valid or not, if it is not it may be marked as fix or no. So three possible outcomes.
My task is to summarize all the sheets into one. The simple solution to me is a countif type function. For some reason the new spreadsheet does not update, once I save it, where I had the correct numbers, I now have errors. When I close and go back in and update, still errors. I am assuming this is because the files are all out on a network drive.
So, the countif solution appears to be out. My next option I was considering was importing the sheets to an Access database, then I could manipulate, query and report from there. However, I don't want to import each SS individually. I can't think of a way to write a macro for it.
I'm sure people do this kind of thing all the time, there has to be a better, less time intensive option. Suggestions?