Combining 5 diff worksheets into one

[pansraohk]

I have 5 worksheets of data relating to 5 different branches in 3 different columns. Each sheet lists around 50 Accounts with Account Number, Account Name and Balance. All 5 branches do not have all the 50 accounts. But the Account Numbers and Account Names are the same.
My task is to bring the balances of the 5 branches into one sheet in 5 different columns against the Account Number and Account Names, show the Sum of Account wise Balances into a new column. I have to also group the accounts into a predefined pattern and arrive at the sub totals for these groups branchwise as wella s for all the 5 branches together.
I shall be obliged for the all the help I can get
Rgds
Rao

 

[sunnyland]

Hello,

I have 6 worksheets in my answer
Summary
Branch1
....
....
....
Branch5

The following formula should work if you have 1 or more lines for each accounts.

accountssummary.xls
	A	B	C	D	E	F	G
1	Account	Details	Branch1	Branch2	Branch3	Branch4	Branch5
2	Account1	Details1	1392	348	0	348	155
3	Account2	Details2	338	338	0	338	31
4	Account3	Details3	0	289	0	289	268
5	Account4	Details4	155	155	155	155	107
6	Account5	Details5	105	0	105	105	136
7	Account6	Details6	187	187	187	187	273
8	Account7	Details7	41	41	41	41	205
9	Account8	Details8	303	303	303	303	167
10	Account9	Details9	69	0	69	69	262
11	Account10	Details10	62	62	62	62	293
Summary

As you can see the formula is :

=SUMIF(INDIRECT(C$1 & "!a1:a100"),$A2,INDIRECT(C$1 & "!c1:c100"))

it uses the 1 row c1:g1 to get the name of the worksheet.

You can see I didn't put details of accounts.

For this you probably can get the full listing that you will import to the summary sheet.

 

[pansraohk]

Thank you and I am sorry I did not see it till today
Just one point I am not clear
While I can generally understand the first part of the formua, in the second part, why !c1:c100 for D$1, E$1, F$1 and G$1 also ?
Rgds
Rao

 

[sunnyland]

Hello,

Because in my workout sample each branch was something like this:

accountssummary.xls
	A	B	C	D
1	Account	AccountName	Balance
2	Account1		348
3	Account1		348
4	Account1		348
5	Account1		348
6	Account2		338
7	Account4		155
8	Account5		105
9	Account6		187
10	Account7		41
11	Account8		303
12	Account9		69
13	Account10		62
14	Account11		104
Branch1

So the first part of the formula was referencing the first colum [accounts] and the 3rd one had the amount to add up