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?

 

[simonf]

Just numbers. Can range from 0 to whatever. Row headings are revenue/expense categories and column headings are months. Thanks.

 

[simonf]

It's just like the spreadsheet from my original post.

 

[Aladin Akyurek]

On 2002-10-29 16:29, simonf wrote:
Just numbers. Can range from 0 to whatever. Row headings are revenue/expense categories and column headings are months. Thanks.

How do you suppose to compute the cell to consolidate from the target/selected worksheets at from such numbers?

 

[simonf]

One question regarding THREED function: If i have Sheet1 to Sheet6, I can't seem to get it to work if I only want to SUMPRODUCT Sheet1 to Sheet4. It'll only do it for Sheet1 to Sheet6. Kind of a all or nothing deal. Is this correct? Thanks.

 

[simonf]

Silly me. I am incorrect. Sorry.