'Merging' 10 Workbooks - HELP

[bderuiter]

I have 10 Excel Shared Workbooks, all in the same format, but with different result information. Each workbook has 16K rows of information, the basic information is the same. For reporting purposes I thought I could just 'merge' the workbooks into a master report so that I could monitor the progress of the work being completed without doing a V-Lookup. However I just discovered that I can merge but it's going to pull all data repeatedly. I just want updated data to flow over as though I was doing a V-Lookup.

The reason I'm avoiding VLookup is because the size of these workbooks is 23MB each (x10) - we're utilizing columns A through AW and I only require about 15 columns to process a return. I am also desperate to find an easier way to report on the data being updated. 10 Shared Workbooks, 90 people working in them and the server it's stored on is several states away. It takes 10+ minutes to unshare 1 workbook so that I can update it and/or get the information needed.

HELP.

 

[wolverineb]

Welcome

There are a lot of variables involved in answering your question.

First, I'm not sure what you mean about being able to merge but it pulls data repeatedly. What method are you referring to?

Second, VLOOKUP would bog things down, but it also doesn't auto-refresh any time a change is made in another workbook. So I'm not sure the flowing thing you refer to is an option anyway.

Third, you could do some automated "open each file - copy what you want - close the file" VBA code that you could run whenever you like. This is what I would do if all the files were on one network. I'm not sure how long it would take on your system. It would be a one-time update and wouldn't bog down the file you work with once the data was all there. But you'd need to rerun the code whenever you wanted your data to be refreshed.

How often are you needing an update? Weekly? Every ten minutes?

 

[bderuiter]

In answer to your questions:

There are 92 different people assigned to the 16+K accounts. Also, because of system issues there is no way for the accounts being worked to be overseen so teams (10 teams, 8 to 10 people each) have their own workbook, in shared form, that they work in and update. It's to complicated to try to split the workbooks only leaving the accounts that are assigned to the teams - so all the workbooks have all the same accounts listed. This means 10 workbooks with the same accounts basics, but due to assignments different information is showing in each workbook.

I want to be able to draw all the updated information into a master workbook - similar to a VLookup so that 'if this/than look at that' and I only draw in the information thats been added.

When I did a test run today to 'merge' two shared workbooks - the result was that it pulled all the cells with information - not if it saw/matched this - then it returned the cell values after.

There has to be an easier way.

 

[wolverineb]

Right - so when you say "I did a test run today to merge two workbooks" - what exactly does that mean?

 

[Fazza]

It sounds like a proper database should be used, not Excel. What do you think?

 

[bderuiter]

It's been awhile since I merged two workbooks - so I followed the directions and tested it today. All cell information pulled into the master workbook.

 

[wolverineb]

Any chance you'll ever tell us what it is you tried or did? Not sure what directions you're referring to.

 

[bderuiter]

I agree, a proper database would be the way to go - however I haven't done anything in Access in years and we're unable to get the support group to put one together for us. We should be able to work in systems sometime in the next few months - but meanwhile I need to figure out the quickest, easiest way to be able to report the data that will be housed. When there was just four teams/four workbooks, it would take nearly 3 to 4 hours once a week. This issue was mainly because of servers and people still showing in the Shared file - having to be removed. Back to your question: Yes, a database would be great but isn't likely. I need a more efficient work-around.

 

[bderuiter]

Sorry - I created two workbooks and Shared them both (per the directions). I created a header row (identical) on both. I put the same information into cell A1, A2, A3 in both workbooks etc.. then I added extra information on the 2nd shared workbook. When I merged the workbooks it pulled all the information from the 2nd workbook, including A1, A2, A3 and placed it in the blank cells (starting at A4).

 

[wolverineb]

Okay. I am going to assume that you are talking about the Excel help instructions on how to merge shared workbooks. I am also going to assume that you have solved your problem.

If not, please clarify.