Summarizing data from multiple workbooks

Vistaman

New Member
Joined
Nov 13, 2005
Messages
3
I am working on a survey project which requires summing the data in the cells of 80 workbooks. Each workbook is one sheet and exactly the same format and layout. Each cell contains one number and no formula. Is there a way to shortcut the summation of these workbooks into a single report without having to cut and paste data from each sheet. The finished product is a summary report on all 80 workbooks.
Thank you.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Hi Vistaman and welcome to the Board,

Yes you can do this with 3D references: Quote from Excel Help:

1. Click the cell where you want to enter the function.
2. Type = (equal sign), enter the name of the function, and then type an opening parenthesis.
3. Click the tab for the first worksheet to be referenced.
4. Hold down SHIFT and click the tab for the last worksheet to be referenced.
5. Select the cell or range of cells to be referenced.
6. Complete the formula, and press ENTER.

For example the formula =SUM(Sheet2:Sheet4!A20) will give you the sum of the numbers in cell A20 on sheets 2 to 4.

Edit: Just re-read your post and see you are looking at 80 Workbooks, not worksheets as I first read it. I must do my homework to check how it works over multiple workbooks.


HTH
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Hi,

No you can't use 3D references across multiple workbooks in the same way they can be used with multiple worksheets, sorry about that, I misled you. To do what you want I think you need to use vba, on which I'm no expert, but I've seen similar questions raised on this Board many times in the past so if you do a search on something like "summary workbook" you should find a lot of help. Sorry I can't help more.
 

Vistaman

New Member
Joined
Nov 13, 2005
Messages
3
Thanks, Fergus
Yes, I'm summarizing 80 workbooks. Would be great if they were worksheets instead. Thinking about converting them, but that may be even more time consuming.
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Hi Vistaman,

That solution crossed my mind, maybe if it was me I would do that. If all workbooks only have one sheet, and each is the same, you could start a new workbook at sheet 1 and in cell A1 put in =[First Workbook.xls]Sheet1!A1 then copy that across and down to capture all the first workbook. Then create a copy at sheet 2 and use find/replace just to change the workbook name and so on until you have all 80 sheets. Then use 3D references on a summary sheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,462
Messages
5,572,260
Members
412,451
Latest member
newbie22922792
Top