Bring together tons of spreadsheets?

[starkmann]

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?

 

[ECTech]

what type of permissions do you have for this network drive?

 

[Leady4]

You have 1 spreadsheet for every day of the year (ie 52 files??)
Or 1 file containing 52 sheets?
Are you opening the new sheet from the same machine?
It sounds just like a problem I had when I did a complicated whatif formula on a speadsheet that worked fine when I used it on my machine but didn't when I used it on anyone elses cos the mapped drives were different

 

[starkmann]

I have read, write, delete type permissions

It is one workbook (one file) per day. so 365 per year. i have seveal years worth. Workbook has one sheet on it so I assume it was some kind of export originally.
I lost to data from the file (it became unlink or the link broke) while working on the file without logging out. all the files are on this shared drive, none of them are on my C drive.
The countif was working great until I save the file. Then it broke. oddly the one field in each row that is calculated from all those link retained its values.