Select and automate

[Tigerexcel]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Low Model								Mid Model
2		Jan	Feb	Mar	Apr	May	Jun			Jan	Feb	Mar	Apr	May	Jun
3	Prod A	15	97	80	74	25	35		Prod A	60	68	38	45	45	69
4	Prod B	34	11	84	52	51	51		Prod B	60	68	20	83	59	41
5	Prod C	10	13	93	22	55	78		Prod C	95	96	25	14	44	49
6	Prod D	78	52	83	57	77	89		Prod D	31	32	98	82	88	96
7
8									High Model
9	Select	Low								Jan	Feb	Mar	Apr	May	Jun
10	Select	High							Prod A	80	90	77	72	90	99
11									Prod B	68	100	93	43	64	90
12									Prod C	24	18	100	50	68	100
13									Prod D	64	56	62	14	95	36
14
15		Jan			Feb			Mar			Apr			May			Jun
16		Low	High	Variance	Low	High	Variance	Low	High	Variance	Low	High	Variance	Low	High	Variance	Low	High	Variance
17	Prod A
18	Prod B
19	Prod C
20	Prod D
Sheet2

Cell Formulas
Range	Formula
B16,Q16,N16,K16,H16,E16	B16	=$B$9
C16,R16,O16,L16,I16,F16	C16	=$B$10

Cells with Data Validation
Cell	Allow	Criteria
B9:B10	List	Low,Mid,High

Trying to calculate variances based on different model selections. I am thinking perhaps the CHOOSE function and INDEX/MATCH to lookup the values from whichever models are selected but any combination of functions that work will do, even thought that named ranges might help but couldn't get them to work.
Cell B17 and C17 should return 15 and 80 etc, because the Low Model and High Model have been selected via B9 and B10.

 

[Fluff]

Myabe like
=IF(B$16="Low",INDEX($B$3:$G$6,MATCH($A17,$A$3:$A$6,0),MATCH($B$15,$B$2:$G$2,0)),IF(B$16="Mid",INDEX($J$3:$O$6,MATCH($A17,$I$3:$I$6,0),MATCH($B$15,$J$2:$O$2,0))))

 

[sandy666]

[ deleted ]

 

[Tigerexcel]

Thanks Fluff, your formula works with the Low and Mid scenarios but not when the High scenario is chosen, I can add an extra IF statement to get it to work. It's very likely that new scenarios will emerge so I do need some flexibility with my formulae so I'd like to avoid hard-coding the scenarios if possible. I've played around with some other functions and though this probably looks cumbersome and could be improved upon I've come up with this version which uses INDIRECT and named ranges. It's not perfect and does require the months to be listed as headings so they can be referenced in the formulae. Interested in hearing alternative solutions, ultimately I'll go with something that is flexible and as compact as possible. If it helps, I've just upgraded to Office 365.

Dynamic scenario v2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Low Model								Mid Model
2		Jan	Feb	Mar	Apr	May	Jun			Jan	Feb	Mar	Apr	May	Jun
3	Prod A	15	97	80	74	25	35		Prod A	60	68	38	45	45	69
4	Prod B	34	11	84	52	51	51		Prod B	60	68	20	83	59	41
5	Prod C	10	13	93	22	55	78		Prod C	95	96	25	14	44	49
6	Prod D	78	52	83	57	77	89		Prod D	31	32	98	82	88	96
7
8									High Model
9	Select	Low								Jan	Feb	Mar	Apr	May	Jun
10	Select	High							Prod A	100	90	77	72	90	99
11									Prod B	68	100	93	43	64	90
12									Prod C	24	18	100	50	68	100
13									Prod D	64	56	62	14	95	36
14
15		Jan	Jan	Jan	Feb	Feb	Feb	Mar	Mar	Mar	Apr	Apr	Apr	May	May	May	Jun	Jun	Jun
16		Low	High	Variance	Low	High	Variance	Low	High	Variance	Low	High	Variance	Low	High	Variance	Low	High	Variance
17	Prod A	15	100	-85	97	90	7	80	77	3	74	72	2	25	90	-65	35	99	-64
18	Prod B	34	68	-34	11	100	-89	84	93	-9	52	43	9	51	64	-13	51	90	-39
19	Prod C	10	24	-14	13	18	-5	93	100	-7	22	50	-28	55	68	-13	78	100	-22
20	Prod D	78	64	14	52	56	-4	83	62	21	57	14	43	77	95	-18	89	36	53
Forum

Cell Formulas
Range	Formula
B16,Q16,N16,K16,H16,E16	B16	=$B$9
C16,R16,O16,L16,I16,F16	C16	=$B$10
B17:C20,E17:F20,H17:I20,K17:L20,N17:O20,Q17:R20	B17	=INDEX(INDIRECT(B$16),MATCH($A17,$A$3:$A$6,0)+1,MATCH(B$15,$B$2:$G$2,0)+1)
D17:D20,G17:G20,J17:J20,M17:M20,P17:P20,S17:S20	D17	=B17-C17

Cells with Data Validation
Cell	Allow	Criteria
B9	List	Low,Mid,High
B10	List	Low,Mid,High

 

[Fluff]

Glad you sorted it & thanks for the feedback.