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?

 

[Aladin Akyurek]

On 2002-10-25 19:42, simonf wrote:
Guess not since a few people will be using this file but if I could use a, which one will you recommend? Thanks.

The morefunc.xll add-in, a free download from:

http://longre.free.fr/index.html

What for? To satisfy your boss...

aaSelectiveConsolidation SimonF.xls
	A	B	C	D	E
1	co.1	1
2		0		14
3	co.3	1
4		0
5		0
6		0
7		0
8		0
9		0
10		0
Summary

A1:A10 is set up with data validation (I believe you have something different, but that doesn't matter much).

This is important: B1:B10 has the following formula:

=(LEN(A1)>0)+0

The consolidation formula is in D2...

=SUMPRODUCT((THREED(co.1:co.10!B1))*(B1:B10))

co.1!B1 is 4

co.3!B1 is 10.

The others have also values, but, as you see, the formula sums just these two as intended.

Aladin

 

[simonf]

I'll give it a try. Thanks.

 

[simonf]

Hummm, interesting add-ins but I guess it won't work if the company names are random non-series such as: apple, orange, banana, grape etc. Any other help will be much appreciated.

 

[Dave Patton]

To simonf

What happened when you tested the suggestion?

----

One can consider

Co. Include
ABC Y 1
BCC N 0
CCC N 0
DDD Y 1
EEE Y 1
FFF N 0

Evaluate include range with a formula such as

=(B19="Y")+0

Revise the range for the consolidation formula as necessary.

Dave
This message was edited by dave patton on 2002-10-26 00:41

 

[simonf]

Book1
	A	B	C	D
1	apple	1
2	ban	1		43
3		0
4	space	1
5		0
6	cow	1
summary

Hey, it works perfectly. I have data in B1 of sheets named:apple, ban, tile, space, dog, cat. And it summed perfectly.

Now, I just have to figure out how to incoporate INDEX(MATCH()) into the formula to return the correct cell value such as B1.

And most importantly, to see if I can use this add-in at all! If not, am I really screwed?

 

[simonf]

Ok, once I get the position of the cell I want using INDEX(MATCH()), how do I translate that row & col back to a cell reference to be used in the THREED formula?

eg. INDEX(B5:Z29, 5, 9) What function can translate this to J9? Thanks.

 

[Aladin Akyurek]

On 2002-10-29 13:57, simonf wrote:
Ok, once I get the position of the cell I want using INDEX(MATCH()), how do I translate that row & col back to a cell reference to be used in the THREED formula?

eg. INDEX(B5:Z29, 5, 9) What function can translate this to J9? Thanks.

Simon,

Glad to hear you succeded in applying the selective consolidation scheme I described.

That said, I don't think I understand the INDEX/MATCH bit. I thought you knew beforehand which cell9S0 from the selected worksheets will be consolidated.

Aladin
This message was edited by aladin akyurek on 2002-10-29 16:06

 

[paliman]

It's not so elegant, but...

=ADDRESS(ROW(INDEX(B5:Z29,5,9)),COLUMN(INDEX(B5:Z29,4,9)),4)

will give you J9

 

[simonf]

Thanks but the INDEX will only return one value instead of the 2 you suggested. As for the known value, I could use that and copy across and down but I want to make it even more flexible incase I have to make changes and that's where the INDEX/MATCH comes in. In that scheme, the INDEX/MATCH will only return one value but the row/col reference is only refering to the INDEX range and not the global position within the entire worksheet.

 

[Aladin Akyurek]

On 2002-10-29 14:58, simonf wrote:
Thanks but the INDEX will only return one value instead of the 2 you suggested. As for the known value, I could use that and copy across and down but I want to make it even more flexible incase I have to make changes and that's where the INDEX/MATCH comes in. In that scheme, the INDEX/MATCH will only return one value but the row/col reference is only refering to the INDEX range and not the global position within the entire worksheet.

What kind of info/data do you have in B5:Z29?