Dynamic formula using named cells by partial name

[wbstadeli]

Hi All,

Is there a formula that I can use the would accommodate the following? Example: I need it to sum all named ranges called "AAA_(unique number)" and then dividing that by all named ranges called "BBB_(unique number)". So the (unique number) at the end of the name is variable, and these named ranges can/will be created after this formula is in place so I cant hard code the complete name in the formula, how would a write the formula to sum all the ranges that "match" a certain amount of the name (for this example, AAA and BBB)?

 

[DRSteele]

I think what you need is function INDIRECT. That will allows you to type in some portions of the named ranged reference into a cell and have the formula report the calculation you seek. For example,

MrExcel posts18.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
5	Named range:	AAA_111		AAA_112			AAA_113
6		6		3			1			AAA_	111	112	113
7		10		5			2
8		5		6			5			one way	AAA_111	AAA_112	AAA_113
9		2		0			6				78	38	85
10		4		6			9
11		9		5			3			another way	78	38	85
12		10		4			1
13		2		6			5
14		8		3			0
15		2		38			5
16		5					1
17		6					9
18		4					8
19		5					8
20		0					5
21		78					0
22							2
23							2
24							10
25							3
26							85
Sheet51

Cell Formulas
Range	Formula
K8:M8	K8	=$J$6&K6
K9:M9	K9	=SUM(INDIRECT(K8))
K11:M11	K11	=SUM(INDIRECT("AAA_"&K6))
D15	D15	=SUM(AAA_112)
B21	B21	=SUM(AAA_111)
G26	G26	=SUM(AAA_113)

Named Ranges
Name	Refers To	Cells
AAA_111	=Sheet51!$B$6:$B$20	B21
AAA_112	=Sheet51!$D$6:$D$14	D15
AAA_113	=Sheet51!$G$6:$G$25	G26