How to consolidate a Large Formula

[Grimm127]

HI All you Gurus out there,

I need a bit of help. I am working on a report that I need to basically get a percentage of revenue. My formula is Dymanic using Index where I can drill down to the account and using a Indirect for the Tab based on the selection of Year.

Below is a formula that I need to sum the correct revenue. The Revenue I need to add at three segments Internal, External and Broker from the Whole. Is there a way I can make this smaller maybe making it into a function?

Another thought is I just create a Macro where it inserts the formula into the cell and then save as values....Thanks in advance for your suggestions.....

=SUMIFS(INDEX(INDIRECT("'"&$BR$3&"'!"&"$W:$AH"),0,N$6),INDIRECT("'"&$BR$3&"'!"&"$L:$L"),$B7,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$4),$BE$4,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$5),$BE$5,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$6),$BE$6,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$7),$BE$7,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$8),$BE$8)-SUMIFS(INDEX(INDIRECT("'"&$BR$3&"'!"&"$W:$AH"),0,N$6),INDIRECT("'"&$BR$3&"'!"&"$L:$L"),$B7,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$4),$BE$4,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$5),$BE$5,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$6),$BE$6,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$7),$BE$7,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$8),$BE$8,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,14),"Brk")+SUMIFS(INDEX(INDIRECT("'"&$BR$3&"'!"&"$W:$AH"),0,N$6),INDIRECT("'"&$BR$3&"'!"&"$L:$L"),$B8,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$4),$BE$4,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$5),$BE$5,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$6),$BE$6,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$7),$BE$7,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$8),$BE$8)

 

[jasonb75]

You can't use a Name for a range that can be confused for a cell reference.

? I knew I was overlooking something ridiculously obvious.

After Kirk mentioned problems I tried the formula, but only with rng, not rng1, etc. Had I done so I would have realised the error in my suggestion.

 

[KRice]

Here is a mock up of the approach if it might be of some use:

Grimm127.xlsx
	A	B	C				G	H					M	N	O	P																																								BD	BE	BF	BG											BR
1							rngA	INDIRECT("'"&$BR$3&"'!"&"$A:$N")
2							rngB	INDIRECT("'"&$BR$3&"'!"&"$W:$AH")																																																														Sheet Name
3							rngC	INDIRECT("'"&$BR$3&"'!"&"$L:$L")																																																	val	col												Sheet 27
4	formula ->	290.1																																																						rngA filter ->	16	1
5														col																																										rngA filter ->	17	2
6		val												2	<- rngB filter																																									rngA filter ->	18	3
7	rngC filter ->	11																																																						rngA filter ->	19	4
8	rngC filter ->	13																																																						rngA filter ->	20	5
Grimm127

Cell Formulas
Range	Formula
B4	B4	=SUM(SUMIFS(INDEX(rngB,0,N$6),rngC,$B7:$B8,INDEX(rngA,0,$BF$4),$BE$4,INDEX(rngA,0,$BF$5),$BE$5,INDEX(rngA,0,$BF$6),$BE$6,INDEX(rngA,0,$BF$7),$BE$7,INDEX(rngA,0,$BF$8),$BE$8,INDEX(rngA,0,14),{"<>Brk";"<>"}))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

And the worksheet referenced in BR3 with some random data, where I've been checking functionality with rows 6,7,11, and 12:

Grimm127.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	rngA											rngC											rngB
2	1	2	3	4	5	6	7	8	9	10	11	12	13	14									1	2	3	4	5	6	7	8	9	10	11	12
3	1	1	1	1	1	1	1	1	1	1	1	1	1	1									1	3	5	7	9	11	13	15	17	19	21	23
4	4	4	4	4	4	4	4	4	4	4	4	4	4	4									1	3	5	7	9	11	13	15	17	19	21	23
5	5	5	5	5	5	5	5	5	5	5	5	5	5	5									1	3	5	7	9	11	13	15	17	19	21	23
6	16	17	18	19	20	3	1	3	1	3	1	11	1	Brk									1	155	5	7	9	11	13	15	17	19	21	23
7	16	17	18	19	20	2	2	2	2	2	2	11	2	2									1	3.1	5	7	9	11	13	15	17	19	21	23
8	3	3	3	3	3	3	3	3	3	3	3	3	3	3									1	3	5	7	9	11	13	15	17	19	21	23
9	6	6	6	6	6	6	6	6	6	6	6	6	6	6									1	3	5	7	9	11	13	15	17	19	21	23
10	9	9	9	9	9	9	9	9	9	9	9	9	9	9									1	3	5	7	9	11	13	15	17	19	21	23
11	16	17	18	19	20	3	1	3	1	3	1	13	1	Brk									1	88	5	7	9	11	13	15	17	19	21	23
12	16	17	18	19	20	3	1	3	1	3	1	13	1	999									1	199	5	7	9	11	13	15	17	19	21	23
13	3	1	3	1	3	1	3	1	3	1	3	1	3	1									1	3	5	7	9	11	13	15	17	19	21	23
14	1	3	1	3	1	3	1	3	1	3	1	3	1	3									1	3	5	7	9	11	13	15	17	19	21	23
15	3	1	3	1	3	1	3	1	3	1	3	1	3	1									1	3	5	7	9	11	13	15	17	19	21	23
16
17
Sheet 27