Consolidating/adding data from several spreadsheets.

[tkhouse]

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.

 

[Jerry Sullivan]

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
	A	B	C	D
1	Week 1
2		Monday	Tue	Wed
3	Walk Ins	9	0	4
Totals-Dec 2010

Excel 2007

Cell Formulas
Range	Formula
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.

 

[tkhouse]

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!!!

 

[tkhouse]

Jerry,
You are a genius; your solution worked perfectly. Thank you so much for your help!!!

 

[Jerry Sullivan]

Aw shucks...it was nuthin' special. Glad it worked for you.