Summarizing data from multiple workbooks

[Vistaman]

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.

 

[Fergus]

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]

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]

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]

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.

 

[Vistaman]

Thanks,
Good solution. I am reminded to use worksheets next time.
VM