complex summing of multiple worksheets

[simonf]

Book1
	A	B	C	D	E	F	G
1			Aug-02	Sep-02	Oct-02	Nov-02	Total
2	Revenue
3		Book	2,288	15,389	29,965	46,926	94,568
4		CD	4,900	4,814	5,377	5,580	20,671
5		DVD	250	238	226	214	927
6		Tape	654	758	868	857	3,137
7
8	GrossRevenue		8,092	21,198	36,436	53,578	119,303
9
10	Expenses
11		Bankcharges	52	85	63	87	287
12		ConsultingFees	4,000	7,520	8,740	9,168	29,428
13		Depreciation	644	225	79	28	976
14		GeneralOffice		54		65	119
15		Professionalfees	478	555	85	657	1,775
16
17	TotalExpenses		5,174	8,439	8,967	10,004	32,584
18
19	NetIncome		2,918	12,759	27,468	43,573	86,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?

 

[zacemmel]

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.]

Consider using Consolidation with named ranges...

Book1
	A	B	C	D	E	F	G	H	I	J
1	Company1	Aug-02	Sep-02	Oct-02	Nov-02	Total
2	Bankcharges	52	85	63	87	287
3	ConsultingFees	4,000	7,520	8,740	9,168	29,428
4	Depreciation	644	225	79	28	976
5	GeneralOffice		54		65	119
6	Professionalfees	478	555	85	657	1,775
7
8	Company2	Aug-02	Sep-02	Oct-02	Nov-02	Total
9	Officeparties	97,880	86,536	93,687	31,281	309,384
10	Politicalpayoffs	51,614	32,917	12,788	51,574	148,893
11	Depreciation	84,818	56,357	81,329	82,145	304,649
12
13	Consolidation	Aug-02	Sep-02	Oct-02	Nov-02	Total
14	Bankcharges	52	85	63	87	287
15	ConsultingFees	4,000	7,520	8,740	9,168	29,428
16	Officeparties	97,880	86,536	93,687	31,281	309,384
17	Politicalpayoffs	51,614	32,917	12,788	51,574	148,893
18	Depreciation	85,462	56,582	81,408	82,173	305,625
19	GeneralOffice		54		65	119
20	Professionalfees	478	555	85	657	1,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]

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.]

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]

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.]

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

 

[simonf]

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

 

[Aladin Akyurek]

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]

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