Summing multiple workbooks

[singcbl]

Hi,

I know I came across this somewhere but I just couldn't recall where I have seen this been done with regard to summing across multiple worksheet.

Here is my problem. I have about 50 workbooks with similar layout and I have a summary workbook that basically sum up all value of these 50 workbooks. That mean in the Summary Workbook, cell A1 will be the summation of Workbook 1 to 50 and that is the same for Cell A2 and so on.

What I want to know if there is a easier way of summing up all these workbooks without having to open all of them and going to individual cell to achieve the desired result.

Many thanks for any suggestion

 

[singcbl]

I have also another thing to do and that is the number of workbooks may be increase from time to time so it is not limited to these 50 workbooks.

Thanks

 

[Fergus]

Do you mean 50 worksheets in the same workbook? or 50 separate workbooks?

If it's 50 worksheets in the same workbook you can use a 3D reference eg. =SUM(Sheet1:Sheet50!A2) will sum the contents of all cells A2 in sheets 1 to 50.

 

[singcbl]

Fergus,

The 50 worksheets are in different workbooks. Although the worksheet name is the same for all 50 workbooks.

 

[Fergus]

Then I don't know of an easy way to do what you want. Personally I would use vba and use some easy system to name the workbooks eg. wkbk1, wkbk2, and then use a loop in the vba to loop through "wkbk"&x where x goes from 1 to 50.

 

[singcbl]

Fergus,

Thanks for your help. I hope one of the gurus can help with my problem. It kinda of urgent for me.