complex summing of multiple worksheets

simonf

Board Regular
Book1
ABCDEFG
1Aug-02Sep-02Oct-02Nov-02Total
2Revenue
3Book2,28815,38929,96546,92694,568
4CD4,9004,8145,3775,58020,671
5DVD250238226214927
6Tape6547588688573,137
7
8GrossRevenue8,09221,19836,43653,578119,303
9
10Expenses
11Bankcharges52856387287
12ConsultingFees4,0007,5208,7409,16829,428
13Depreciation6442257928976
14GeneralOffice5465119
15Professionalfees478555856571,775
16
17TotalExpenses5,1748,4398,96710,00432,584
18
19NetIncome2,91812,75927,46843,57386,719
20
Happy

Hi, I have multiple worksheets each containing a financial statement like the above. Each worksheet has a different company name. Each worksheet's type of revenue and expenses could be different.

I need to combine all of them into a worksheet of predetermined format, thus pivot table is not a consideration. All the worksheets and the summary worksheet is in the same workbook.

In the summary worksheet, I have a check box to select which company I want to be included in the summary. So everytime I check a company, the name would appear in A1:A10. The way the formula is setup now is a multiple IF statements hard coded in each cell. ie. IF A1=HAPPY,HAPPY!D14,0)+IF(A2=SAD,SAD!D14,0+IF(A3=MAD,MAD!D14,0)+IF(A4=JOY,JOY!D14,0)

So the more companies I have, the more IF I have to add-on. Now, I can use the INDEX(MATCH()) to grab the info I need from each company to the summary worksheet.

The problem is how to sum each number according to which companies are selected.

I hope I'm explaining it clearly. Is there a formula or VBA procedure to look at a range of worksheet names and sum a particular cells from the different worksheets?

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

zacemmel

Well-known Member
Try this formula:

=SUM(Sheet1:Sheet3!A1)

This may not work in your case because you may have to use array type formulas to accomplish what you are trying to do.

Mark W.

MrExcel MVP
Consider using Consolidation with named ranges...
Book1
ABCDEFGHIJ
1Company1Aug-02Sep-02Oct-02Nov-02Total
2Bankcharges52856387287
3ConsultingFees4,0007,5208,7409,16829,428
4Depreciation6442257928976
5GeneralOffice5465119
6Professionalfees478555856571,775
7
8Company2Aug-02Sep-02Oct-02Nov-02Total
9Officeparties97,88086,53693,68731,281309,384
10Politicalpayoffs51,61432,91712,78851,574148,893
11Depreciation84,81856,35781,32982,145304,649
12
13ConsolidationAug-02Sep-02Oct-02Nov-02Total
14Bankcharges52856387287
15ConsultingFees4,0007,5208,7409,16829,428
16Officeparties97,88086,53693,68731,281309,384
17Politicalpayoffs51,61432,91712,78851,574148,893
18Depreciation85,46256,58281,40882,173305,625
19GeneralOffice5465119
20Professionalfees478555856571,775
21
Sheet4

See the Excel Help topics for "Consolidate data" and "Guidelines for specifying source areas for a consolidation".
This message was edited by Mark W. on 2002-10-25 17:20

simonf

Board Regular
Thanks but it still doesn't solve the main problem of how to sum only the worksheets that are selected. ie. summing different worksheets when different companies are selected instead of summing all the companies at once.

Mark W.

MrExcel MVP
On 2002-10-25 17:25, simonf wrote:
Thanks but it still doesn't solve the main problem of how to sum only the worksheets that are selected. ie. summing different worksheets when different companies are selected instead of summing all the companies at once.

Why wouldn't you use named ranges such as...

Company1!Expenses
Company2!Expenses

Company1!Revenues
Company2!Revenues

simonf

Board Regular
Guess I'm not making the situation clear enough. Say if I select from the checkbox co.1,2,5,9 , they will appear in A1:A9 of the summary worksheet as follows:

A1=co.1
A2=co.2
A3=blank
A4=co.5
A5=blank
A6=blank
A7=blank
A8=co.9
A9=blank

Now, what is needed is a formula or procedure to look at A1:A8 and sum only the worksheets indicated. Each worksheet is named after the company such as co.1, co.5, etc.

In this case it'll sum from worksheets co.1,2,5,9. If I pick co.2,4,6,9, it'll only sum from worksheets co.2,4,6,9. Of course I can use multiple IF's like in my original post but is there a better way? Thanks.

Mark W.

MrExcel MVP
I see... I was hoping to get you to "give up" the check boxes, and use Excel's Consolidation feature.

simonf

Board Regular
Yes, I know. It a inherited workbook and my boss like this pick and choose feature. Oh, well.

MrExcel MVP
On 2002-10-25 18:15, simonf wrote:
Guess I'm not making the situation clear enough. Say if I select from the checkbox co.1,2,5,9 , they will appear in A1:A9 of the summary worksheet as follows:

A1=co.1
A2=co.2
A3=blank
A4=co.5
A5=blank
A6=blank
A7=blank
A8=co.9
A9=blank

Now, what is needed is a formula or procedure to look at A1:A8 and sum only the worksheets indicated. Each worksheet is named after the company such as co.1, co.5, etc.

In this case it'll sum from worksheets co.1,2,5,9. If I pick co.2,4,6,9, it'll only sum from worksheets co.2,4,6,9. Of course I can use multiple IF's like in my original post but is there a better way? Thanks.

Are you allowed to use a free third-party add-in?

simonf

Board Regular
Guess not since a few people will be using this file but if I could use a, which one will you recommend? Thanks.

Replies
1
Views
136
Replies
0
Views
272
Replies
5
Views
208
Replies
0
Views
335
Replies
0
Views
337

1,181,105
Messages
5,928,098
Members
436,588
Latest member
mummabare

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.

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