Combining 5 diff worksheets into one

pansraohk

New Member
Joined
Oct 8, 2006
Messages
4
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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
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
Joined
Oct 8, 2006
Messages
4
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
Joined
Jan 27, 2006
Messages
912
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
 

Forum statistics

Threads
1,136,995
Messages
5,679,037
Members
419,801
Latest member
spinjector

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top