Transfering files from lots of files to one single Exel master file.

This sounds like a chaotic work process. Any chance the files being sent out to various people have DIFFERENT information in them?

If they all have the same info and anyone can make changes and send it back, how in the world will you know which change to keep when examining all the sheets coming back?

If the data coming back from the various sheets is different, and the master sheet represents the compilation of all those separate sheets being sent out, now we're talking. This is a much better work process and confusion over which answers are correct are no longer an issue.

What needs to exist in both the master sheet and the subordinate project sheets is one "key" piece of data that identifies which row of data is a "match" between the two sheets. If a project ID does not exist in these docs, let's add them in to streamline this process.

Then it's a simple process to receive files into a specific folder on your computer and run an "Update" macro that opens each file in that folder, spots each unique project ID and brings in the data from that file into the master, closes and repeats with the next file until done.

If everyone is getting the exact same file, you've got a work process problem and you've probably already figured that out. You'll have to think through how you (the human) would make the determination of which data is to be copied into the master from each "copy" of the same file and which is not. It has to be thoroughly explained to the macro or it will won't be right.

I hope you opt to send out specific sheets to the project heads rather than the same sheet to everyone.
========

NOTE: It's just as easy to use the MASTER sheet to CREATE these "project sheets" to send out to the project heads. A macro could parse out the one file to several sheets...again based on some information in the master file itself...perhaps a department column, or a project head by name.

This way it's a two way process.

If helpful, click on GO ADVANCED and use the paperclip icon to upload a sample of your MASTER workbook and your PROJECT workbook going out to someone. WE can look at it and make more specific suggestions.

Originally Posted by yjacob

thought id let you know that i think that update macro sounds like a good approach. But how do i go about making that macro and how does it exactly work? Lets say my master and my template are the same document, but master just updates the data from the 100s of templates i get back from all the different project leaders here. Could update just take out the single set of data from all those files?

Back in post #2 I stated:

Originally Posted by jbeaucaire

If everyone is getting the exact same file, you've got a work process problem and you've probably already figured that out. You'll have to think through how you (the human) would make the determination of which data is to be copied into the master from each "copy" of the same file and which is not. It has to be thoroughly explained to the macro or it will won't be right.

All you've managed to isolate so far is that the only data you want to copy is from column AT. That's great, but you need to provide detailed instructions on the logic Excel is to use...Excel is a dumb child and must be given absolute instructions each and every time.

1) How is data matched between books? Is there a column with a unique Project ID that can be used to make sure the data being copied ends up on the right row in the master?

2) What if multiple sheets coming back have data in Column AT for the same project?

3) What if the Master already has data in AT for a project and a sheet coming back has data in AT for the same project? Which data is kept? Think this this one through completely...is there a situation where the answer is different from one situation to the next?

Modytrane is right, posting sample workbook(s) could make this a much simpler process, yes?

Originally Posted by yjacob

I am not allowed to post it up as the data is confidential and cannot be sent out of the computer.

Bah, sanitizing a sample sheet from an actual dataset takes less than 5 minutes. Certainly takes less time than you're spending trying to explain all this long route...

1) Change the names - enter NAME1 over the first name and then doubleclick the lower right corner of that cell and it copies NAME2, NAME3 etc down for you automatically.

2) Change the addresses - enter ADDRESS1 over the first address, doubleclick the lower right corner to copy it down, too.

3) Change phone numbers - same as above

4) Change the project names - same as above

At this point, no one has any idea on the planet where the numbers left on the sheet could possibly be from, so you could be done at this point, yes? If not, jumble up some of the numbers, too.

Originally Posted by yjacob

Hey thanks for the swift reply. Well there will only be one opportunity for some1 to put data in column AT for that project. There wont be multiple people putting in data in the same cell as only one person has the authority to actually put the data in that cell. So for project 1 only one person at one time will put data in AT1 and for project 2 only one person at one time will put data in AT2. But after AT column has been filled out the next month some1 will put data in AU1 and AU2 etc as that would be the next month.

OK, so the macro ISN'T copying data from column AT after all? That's a critical difference, isn't it? So you'll need some way of also indicating which column to use each time?

Sorry, but your assurance that only one person can enter data one time is not something I would trust. Excel follows instructions to the letter each and every time it is run...humans don't. So as long as you have humans in the mix you will need to instruct Excel what to do when faced with two cells with data in them. It is going to happen...you don't want your macro to just crap out do you?