Consolidating/adding data from several spreadsheets.

tkhouse

New Member
Joined
Jan 11, 2011
Messages
4
I have 9-10 people who track their daily activities on separate spreadsheets and turn them in at the end of the month. The format is the same for each spreadsheet or at least started out the same although some may periodically add lines for notes/information. Basically, I need to consolidate the daily and monthly totals from each spreadsheet into a master spreadsheet that looks the same as the original. I am open to changing the format if necessary in order to facilitate the process. I am using 2003. I replied to a previous post initially with my question, here is the link: http://www.mrexcel.com/forum/newrepl...te=1&p=2570960.

Here is a sample spreadsheet:
Counts

sample removed per OP request, Private Information

Ideally I want to consolidate all the spreadsheets into a master with everyone's totals added together.
 
Last edited by a moderator:
It will be much easier if all the worksheets have the values in the same cells. You could ensure that by distributing templates that have protected worksheets; however since you only have 9-10 worksheets per month to consolidate, you could opt to just visually check to see that cells haven't been moved.

If the sheets are consistent, here is one way to setup a workbook that makes it easy to consolidate the data each month:

A. Create a workbook that has these three worksheets in this order:
1. Your Summary Sheet
2. A Blank sheet
3. Your Template sheet (w/ no values entered in the data cells)
B. Insert this formula into Cell B3 of your Summary sheet:
=SUM(Blank:Template!B3)
It Sums Cell B3 across all your worksheets.

C. Copy that formula to all other cells you want to total on your Summary Sheet.

Excel Workbook
ABCD
1Week 1
2MondayTueWed
3Walk Ins904
Totals-Dec 2010
Excel 2007
Cell Formulas
RangeFormula
B3=SUM(Blank:Template!B3)
C3=SUM(Blank:Template!C3)
D3=SUM(Blank:Template!D3)


When you receive the 9-10 worksheets, copy them all into your workbook After Sheet Blank and Before Sheet Template. They will be totaled on your Summary Sheet

Hope this helps.
 
Upvote 0
I truly appreciate you taking the time to look at this for me and I'm so relieved that there looks to be a relatively easy way to do this! I will give it a try and let you know how it works. thanks again!!!
 
Last edited:
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top