Picking up values from various sheets in a workbook to a summary sheet

[Arvind Kumar Saini]

Hey,

I have to make a summary of the amounts located in various different sheet to a common summary sheet, all sheet has its own summary, but manually linking is cumbersome task for more than 100 branches.
Have tried to prepare the format on what data I need to make.
Please if anyone could suggest some formula or help me on the sample attached.

Thanks

 

[Luke M]

Hi Arvind,

The branch sheets do not all have the exact same layout. This is going to prevent you from being able to effectively summarize your data.
http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm

 

[Arvind Kumar Saini]

Hey Thanks Luke,
the problem is this is a software generated data set.
However is there any formula to pick up the data from summary part only,

 

[Luke M]

It would be hard. Would you mind going in your sample and filling in manually which cells the summary should be looking at, so I know where to pull?
E.g.,
=SUM(Branch1!M7:M10)

 

[Arvind Kumar Saini]

I made the summary manually in this sample, to make it clear what data we need to pull in summary sheet.
I hope this works out, else have to go through hard way to make changes

Thanks Luke

 

[Luke M]

I think I found a solution. It's ugly and volatile (meaning it causes sheet to recalculate every time you make a chance), but it works.

 

[Arvind Kumar Saini]

Wow !! Simply superb... you guys are amazing...
Will go through the file and try to understand this