ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Trouble merging data in shared workbooks... (https://www.excelbanter.com/excel-worksheet-functions/239513-trouble-merging-data-shared-workbooks.html)

Elizabeth

Trouble merging data in shared workbooks...
 
I have a shared workbook with 9 tabs (worksheets). This workbook has to go
out to 18 different people and they have to add line items to each sheet and
e-mail the entire workbook back to me. Then I have to consolidate all 18
workbooks (which adds up to 162 worksheets, all total) into one weekly master
workbook. And while I could probably come up with several better ways to do
this, the directive I've been given is "Thou shalt used Excel spreadsheets to
manage this data." **sigh**

So I built my master workbook and "shared" it. Then I e-mailed it to a 2
co-workers who volunteered to be guinea pigs and they did their additionas
and e-mailed the test-run workbooks back to me. I saved them all to the same
folder on my hard drive and clicked the "Compare and merge workbooks" command
in my master. I chose the two workbooks I wanted to merge into the master
and clicked go. But when it was done, the 2nd workbook had overwritten the
data from the first, as opposed to adding to it.

In other words, there were 49 line items in worksheet #1 of E's workbook and
5 line items in worksheet #1 of M's workbook. After the merge, worksheet #1
of the master workbook only contained 49 line items, instead of the 54 I was
expecting to see.

I REALLY don't want to have to cut and paste data from the 9 tabs of 18
different workbooks into one big workbook each week. There's got to be a way
to automatically transfer all of the data on the different worksheets of the
18 workbooks into one master workbook. If anyone has any idea how to do
this, I'm open to suggestions. I can't create a true "shared" workbook out
on a network folder due to security restrictions (and co-workers that don't
know how to use Excel well enough to trust them not to wipe out the master).
So sending these out via e-mial is the only option I currently have in terms
of gathering their data.

A thousand thank-you's to anyone willing to toss out some ideas on this one.
I'm feeling pretty desperate.

Thanks,
E-


All times are GMT +1. The time now is 10:49 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com