Bring together tons of spreadsheets? - Techist - Tech Forum

Go Back   Techist - Tech Forum > Computer Software > Microsoft Windows and Software
Click Here to Login
Closed Thread
Thread Tools Display Modes
Old 01-17-2007, 12:57 PM   #1 (permalink)
Junior Techie
Join Date: Jun 2006
Posts: 49
Default 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?

starkmann is offline  
Old 01-17-2007, 03:45 PM   #2 (permalink)
Join Date: Jul 2005
Posts: 633

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

ECTech is offline  
Old 01-17-2007, 05:54 PM   #3 (permalink)
Junior Techie
Join Date: Jul 2006
Posts: 40

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
Fear leads to anger ... Anger leads to stress ... Stress leads to doobies ... and doobies lead to twinkies
Leady4 is offline  
Old 01-17-2007, 06:50 PM   #4 (permalink)
Junior Techie
Join Date: Jun 2006
Posts: 49

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.
starkmann is offline  
Closed Thread

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Copyright 2002- Social Knowledge, LLC All Rights Reserved.

All times are GMT -5. The time now is 09:46 PM.

Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2018, vBulletin Solutions, Inc.