# Combining 5 diff worksheets into one

#### pansraohk

##### New Member
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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### sunnyland

##### Well-known Member
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
ABCDEFG
1AccountDetailsBranch1Branch2Branch3Branch4Branch5
2Account1Details113923480348155
3Account2Details2338338033831
4Account3Details302890289268
5Account4Details4155155155155107
6Account5Details51050105105136
7Account6Details6187187187187273
8Account7Details741414141205
9Account8Details8303303303303167
10Account9Details96906969262
11Account10Details1062626262293
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

##### New Member
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

##### Well-known Member
Hello,

Because in my workout sample each branch was something like this:
accountssummary.xls
ABCD
1AccountAccountNameBalance
2Account1348
3Account1348
4Account1348
5Account1348
6Account2338
7Account4155
8Account5105
9Account6187
10Account741
11Account8303
12Account969
13Account1062
14Account11104
Branch1

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

Replies
0
Views
886
Replies
7
Views
301
Replies
1
Views
515
Replies
1
Views
456
Replies
3
Views
316

1,170,933
Messages
5,872,792
Members
432,947
Latest member
tchurzofretka

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back