To me its complex

[Zubair]

Dear Experts,

I am trying to get results along with changing headings in ROW 7 to 14 based on the selection of B2:B6 from the array C19:AX24.

I need formula only to bring headings based on the selection in B2:B6 in rows 7 to 9, whereas I have applied formula from row 10 to 14 please do correction in case my formula need any change.

Please don't suggest VBA code or pivot table options.

View 1 - as shown below, I have selected View = Years by Scenario, Number of Scenario = Scenario 2 so I need 2 if I select 4 than I need 4 Scenarios, Number of Years = 2 similarly if I select 4 it should give 4 years data, Sector = 2 if I put 3 all three sectors needs to be there.

View 1

Mr. Excel.xlsm
	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
1	User selection
2	View	Scenario by years		View No.1
3	Number of Scenario	Scenario 2
4	Number of of Years	Y4
5	Sector	3
6	Number of columns
7			Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2
8			Y1	Y1	Y1	Y2	Y2	Y2	Y3	Y3	Y3	Y4	Y4	Y4	Y1	Y1	Y1	Y2	Y2	Y2	Y3	Y3	Y3	Y4	Y4	Y4
9			DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF
10	Sales		1,000	1,100	1,200	1,060	1,070	1,080	1,180	1,190	2,000	2,000	1,290	1,234	1,010	1,210	900	1,090	1,100	1,110	2,010	2,020	2,030	1,002	987	3,000	-	-	-	-
11	Cost		111	110	123	180	900	876	1,111	444	222	900	345	343	230	140	980	765	875	170	2,222	2,134	2,000	990	123	2,343	-	-	-	-
12	GP		889	990	1,077	880	170	204	69	746	1,778	1,100	945	891	780	1,070	(80)	325	225	940	(212)	(114)	30	12	864	657	-	-	-	-
13	Expense		100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	-	-	-	-
14	NP		789	890	977	780	70	104	(31)	646	1,678	1,000	845	791	680	970	(180)	225	125	840	(312)	(214)	(70)	(88)	764	557	-	-	-	-
15
16			Scenario 1	Scenario 1	Scenario 1	Scenario 2	Scenario 2	Scenario 2	Scenario 3	Scenario 3	Scenario 3	Scenario 4	Scenario 4	Scenario 4	Scenario 1	Scenario 1	Scenario 1	Scenario 2	Scenario 2	Scenario 2	Scenario 3	Scenario 3	Scenario 3	Scenario 4	Scenario 4	Scenario 4	Scenario 1	Scenario 1	Scenario 1	Scenario 2	Scenario 2	Scenario 2	Scenario 3
17			Y1	Y1	Y1	Y1	Y1	Y1	Y1	Y1	Y1	Y1	Y1	Y1	Y2	Y2	Y2	Y2	Y2	Y2	Y2	Y2	Y2	Y2	Y2	Y2	Y3	Y3	Y3	Y3	Y3	Y3	Y3
18			DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF
19	Sales		1,000	1,100	1,200	1,010	1,210	900	1,020	1,111	789	1,030	1,040	1,050	1,060	1,070	1,080	1,090	1,100	1,110	1,120	1,130	1,140	1,150	1,160	1,170	1,180	1,190	2,000	2,010	2,020	2,030	2,040
20	Cost		111	110	123	230	140	980	333	500	800	120	145	160	180	900	876	765	875	170	267	900	1,432	876	621	999	1,111	444	222	2,222	2,134	2,000	657
21	GP		889	990	1,077	780	1,070	(80)	687	611	(11)	910	895	890	880	170	204	325	225	940	853	230	(292)	274	539	171	69	746	1,778	(212)	(114)	30	1,383
22	Expense		100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
23	NP		789	890	977	680	970	(180)	587	511	(111)	810	795	790	780	70	104	225	125	840	753	130	(392)	174	439	71	(31)	646	1,678	(312)	(214)	(70)	1,283
24	Rank		18	11	8	23	9	43	25	27	42	14	15	17	19	37	35	31	34	13	21	33	48	32	28	36	39	24	2	47	45	40	5
Multiple formulas (2)

Cell Formulas
Range	Formula
C10:AD11,C13:AD13	C10	=SUMIFS($C19:$AX19,$C$16:$AX$16,C$7,$C$17:$AX$17,C$8,$C$18:$AX$18,C$9)
C12:AD12,C14:AD14,C23:AG23,C21:AG21	C12	=C10-C11
C24:AG24	C24	=RANK.EQ(C23,$C$23:$AX$23,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C24:AX24	Other Type	Color scale		NO
C23:AX23	Cell Value	duplicates	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2	List	Scenario by years, Years by scenario, Most Profitable
B3	List	=$A$27:$A$46
B4	List	=$B$27:$B$46
B5	List	=$C$27:$C$29
B6	List	=$E$27:$E$75

View 2

Mr. Excel.xlsm
	A	B	C	D	E	F	G	H	I	J
1	User selection
2	View	Years by scenario		View No.2
3	Number of Scenario	Scenario 2
4	Number of of Years	Y2
5	Sector	2
6	Number of columns
7			Scenario 1	Scenario 1	Scenario 2	Scenario 2	Scenario 1	Scenario 1	Scenario 2	Scenario 2
8			Y1	Y1	Y1	Y1	Y2	Y2	Y2	Y2
9			DF	SF	DF	SF	DF	SF	DF	SF
10	Sales		1,000	1,100	1,010	1,210	1,060	1,070	1,090	1,100
11	Cost		111	110	230	140	180	900	765	875
12	GP		889	990	780	1,070	880	170	325	225
13	Expense		100	100	100	100	100	100	100	100
14	NP		789	890	680	970	780	70	225	125
Multiple formulas (3)

Cell Formulas
Range	Formula
C10:J11,C13:J13	C10	=SUMIFS($C19:$AX19,$C$16:$AX$16,C$7,$C$17:$AX$17,C$8,$C$18:$AX$18,C$9)
C12:J12,C14:J14	C12	=C10-C11

Cells with Data Validation
Cell	Allow	Criteria
B2	List	Scenario by years, Years by scenario, Most Profitable
B3	List	=$A$27:$A$46
B4	List	=$B$27:$B$46
B5	List	=$C$27:$C$29
B6	List	=$E$27:$E$75

View 3

Mr. Excel.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	User selection
2	View	Most Profitable		View No.3
3	Number of Scenario
4	Number of of Years
5	Sector
6	Number of columns	20
7			Scenario 3	Scenario 1	Scenario 3	Scenario 4	Scenario 3	Scenario 4	Scenario 1	Scenario 1	Scenario 2	Scenario 4	Scenario 1	Scenario 1	Scenario 2	Scenario 4	Scenario 4	Scenario 1	Scenario 4	Scenario 1	Scenario 1	Scenario 2
8			Y3	Y3	Y3	Y3	Y3	Y4	Y4	Y1	Y1	Y4	Y1	Y4	Y2	Y1	Y1	Y4	Y1	Y1	Y2	Y4
9			CF	CF	SF	DF	DF	DF	DF	CF	SF	SF	SF	SF	CF	DF	SF	CF	CF	DF	DF	SF
10	Sales		2,060	2,000	2,050	1,900	2,040	1,890	2,000	1,200	1,210	2,345	1,100	1,290	1,110	1,030	1,040	1,234	1,050	1,000	1,060	987
11	Cost		281	222	300	456	657	555	900	123	140	1,290	110	345	170	120	145	343	160	111	180	123
12	GP		1,779	1,778	1,750	1,444	1,383	1,335	1,100	1,077	1,070	1,055	990	945	940	910	895	891	890	889	880	864
13	Expense		100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
14	NP		1,679	1,678	1,650	1,344	1,283	1,235	1,000	977	970	955	890	845	840	810	795	791	790	789	780	764
Multiple formulas

Cell Formulas
Range	Formula
C10:V11,C13:V13	C10	=SUMIFS($C19:$AX19,$C$16:$AX$16,C$7,$C$17:$AX$17,C$8,$C$18:$AX$18,C$9)
C12:V12,C14:V14	C12	=C10-C11

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C14:V14	Other Type	Color scale		NO

Cells with Data Validation
Cell	Allow	Criteria
B2	List	Scenario by years, Years by scenario, Most Profitable
B3	List	=$A$27:$A$46
B4	List	=$B$27:$B$46
B5	List	=$C$27:$C$29
B6	List	=$E$27:$E$75

 

[Zubair]

Seems its complex to others as well !

 

[Zubair]

Hi Team,

I managed to get formula for 1st view i.e. Scenario by years and trying to include other 2, in the mean time please guide if you can help me to complete my task.

Excel help 9.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	User selection
2	View	Scenario by years
3	Number of Scenarios	2
4	Number of of Years	3
5	Business Model	3
6	Number of columns	7
7	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2
8	Y1	Y1	Y1	Y2	Y2	Y2	Y3	Y3	Y3	Y1	Y1	Y1	Y2	Y2	Y2	Y3	Y3	Y3
9	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF
10	Output required
11	View	Scenario by years
12	Number of Scenarios	2
13	Number of of Years	3
14	Business Model	3
15	Number of columns
16	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2
17	Y1	Y1	Y1	Y2	Y2	Y2	Y3	Y3	Y3	Y1	Y1	Y1	Y2	Y2	Y2	Y3	Y3	Y3
18	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF
19	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok
20
21	View	Years by scenario
22	Number of Scenarios	2
23	Number of of Years	4
24	Business Model	2
25	Number of columns
26	Scenario 1	Scenario 1	Scenario 2	Scenario 2	Scenario 1	Scenario 1	Scenario 2	Scenario 2	Scenario 1	Scenario 1	Scenario 2	Scenario 2	Scenario 1	Scenario 1	Scenario 2	Scenario 2
27	Y1	Y1	Y1	Y1	Y2	Y2	Y2	Y2	Y3	Y3	Y3	Y3	Y4	Y4	Y4	Y4
28	DF	SF	DF	SF	DF	SF	DF	SF	DF	SF	DF	SF	DF	SF	DF	SF
29	ok	ok	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
Summary

Cell Formulas
Range	Formula
A7:R7	A7	=IF($B$2="Scenario by years",IF(COLUMN(A1) <= $B$3 * $B$4 * $B$5, "Scenario " & CEILING((COLUMN(A1)-COLUMN($A$1)+1) / ($B$4 * $B$5), 1), ""),"")
A8:R8	A8	=IF($B$2="Scenario by years",IF(COLUMN(A1) <= $B$3 * $B$4 * $B$5, "Y" & MOD(INT((COLUMN(A1)-COLUMN($A$1))/ $B$5), $B$4) + 1, ""),"")
A9:R9	A9	=IF($B$2="Scenario by years",IF(A8<>"",CHOOSE(MOD(COLUMN(A1)-COLUMN($A$1),$B$5)+1,"DF","SF","CF"),""),"")
A19:R19	A19	=IF(A7=A16,IF(A8=A17,IF(A9=A18,"ok","FALSE")))
A29:P29	A29	=IF(A7=A26,IF(A8=A27,IF(A9=A28,"ok","FALSE")))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B6	Expression	=B2<>"Most Profitable"	text	NO
B5	Expression	=B2="Most Profitable"	text	NO
B4	Expression	=B2="Most Profitable"	text	NO
B3:B5	Expression	=B2="Most Profitable"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2	List	Scenario by years, Years by scenario, Most Profitable
B3	List	=IF($B$2="Most Profitable","",Control!D2:D21)
B4	List	=IF($B$2="Most Profitable","",Control!F2:F21)
B5	List	=IF($B$2="Most Profitable","",Control!H2:H4)
B6	List	=IF($B$2="Most Profitable",Control!I2:I49,"")

Excel help 9.xlsx
	A	B	C	D	E	F	G	H	I
1	View	List1	Number of Scenario	List2	Number of Years	List3	Business Model	List4	Number of columns
2	Scenario by years	SY	Scenario 1	1	Y1	1	DF	1	1
3	Years by scenario	YS	Scenario 2	2	Y2	2	SF	2	2
4	Most Profitable	MP	Scenario 3	3	Y3	3	CF	3	3
5			Scenario 4	4	Y4	4			4
6			Scenario 5	5	Y5	5			5
7			Scenario 6	6	Y6	6			6
8			Scenario 7	7	Y7	7			7
9			Scenario 8	8	Y8	8			8
10			Scenario 9	9	Y9	9			9
11			Scenario 10	10	Y10	10			10
12			Scenario 11	11	Y11	11			11
13			Scenario 12	12	Y12	12			12
14			Scenario 13	13	Y13	13			13
15			Scenario 14	14	Y14	14			14
16			Scenario 15	15	Y15	15			15
17			Scenario 16	16	Y16	16			16
18			Scenario 17	17	Y17	17			17
19			Scenario 18	18	Y18	18			18
20			Scenario 19	19	Y19	19			19
21			Scenario 20	20	Y20	20			20
22									21
23									22
24									23
25									24
26									25
27									26
28									27
29									28
30									29
31									30
32									31
33									32
34									33
35									34
36									35
37									36
38									37
39									38
40									39
41									40
42									41
43									42
44									43
45									44
46									45
47									46
48									47
49									48
Control

 

[Zubair]

Now I am able to manage 2nd view also, with changing dropdown its giving expected results.
Now the difficult part to include formula to bring Most profitable heading based on data validation to work, please share if you have any easy way.

Excel help 9.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	User selection
2	View	Years by scenario
3	Number of Scenarios	2
4	Number of of Years	4
5	Business Model	2
6	Number of columns	7
7	Scenario 1	Scenario 1	Scenario 2	Scenario 2	Scenario 1	Scenario 1	Scenario 2	Scenario 2	Scenario 1	Scenario 1	Scenario 2	Scenario 2	Scenario 1	Scenario 1	Scenario 2	Scenario 2
8	Y1	Y1	Y1	Y1	Y2	Y2	Y2	Y2	Y3	Y3	Y3	Y3	Y4	Y4	Y4	Y4
9	DF	SF	DF	SF	DF	SF	DF	SF	DF	SF	DF	SF	DF	SF	DF	SF
10	Output required
11	View	Scenario by years
12	Number of Scenarios	2
13	Number of of Years	3
14	Business Model	3
15	Number of columns
16	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 1	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2	Scenario 2
17	Y1	Y1	Y1	Y2	Y2	Y2	Y3	Y3	Y3	Y1	Y1	Y1	Y2	Y2	Y2	Y3	Y3	Y3
18	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF
19	ok	ok	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
20
21	View	Years by scenario
22	Number of Scenarios	2
23	Number of of Years	4
24	Business Model	2
25	Number of columns
26	Scenario 1	Scenario 1	Scenario 2	Scenario 2	Scenario 1	Scenario 1	Scenario 2	Scenario 2	Scenario 1	Scenario 1	Scenario 2	Scenario 2	Scenario 1	Scenario 1	Scenario 2	Scenario 2
27	Y1	Y1	Y1	Y1	Y2	Y2	Y2	Y2	Y3	Y3	Y3	Y3	Y4	Y4	Y4	Y4
28	DF	SF	DF	SF	DF	SF	DF	SF	DF	SF	DF	SF	DF	SF	DF	SF
29	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok
Summary

Cell Formulas
Range	Formula
A7:R7	A7	=IF($B$2="Years by Scenario",IF(COLUMN(A1)<=$B$3*$B$4*$B$5,"Scenario "&MOD(INT((COLUMN(A1)-COLUMN($A$1))/$B$5),$B$3)+1,""),IF($B$2="Scenario by years",IF(COLUMN(A1)<=$B$3*$B$4*$B$5,"Scenario "&CEILING((COLUMN(A1)-COLUMN($A$1)+1)/($B$4*$B$5),1),""),""))
A8:R8	A8	=IF($B$2="Scenario by years",IF(COLUMN(A1)<=$B$3*$B$4*$B$5,"Y"&MOD(INT((COLUMN(A1)-COLUMN($A$1))/$B$5),$B$4)+1,""),IF($B$2="Years by Scenario",IF(COLUMN(A1)<=$B$3*$B$4*$B$5,"Y"&CEILING((COLUMN(A1)-COLUMN($A$1)+1)/($B$3*$B$5),1),""),""))
A9:R9	A9	=IF($B$2="Scenario by years", IF(A8<>"", CHOOSE(MOD(COLUMN(A1)-COLUMN($A$1), $B$5)+1, "DF", "SF", "CF"), ""), IF($B$2="Years by Scenario", IF(A8<>"", CHOOSE(MOD(COLUMN(A1)-COLUMN($A$1), $B$5)+1, "DF", "SF", "CF"), ""), ""))
A19:R19	A19	=IF(A7=A16,IF(A8=A17,IF(A9=A18,"ok","FALSE")))
A29:P29	A29	=IF(A7=A26,IF(A8=A27,IF(A9=A28,"ok","FALSE")))
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B6	Expression	=B2<>"Most Profitable"	text	NO
B5	Expression	=B2="Most Profitable"	text	NO
B4	Expression	=B2="Most Profitable"	text	NO
B3:B5	Expression	=B2="Most Profitable"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2	List	Scenario by years, Years by scenario, Most Profitable
B3	List	=IF($B$2="Most Profitable","",Control!D2:D21)
B4	List	=IF($B$2="Most Profitable","",Control!F2:F21)
B5	List	=IF($B$2="Most Profitable","",Control!H2:H4)
B6	List	=IF($B$2="Most Profitable",Control!I2:I49,"")

 

[Zubair]

I managed to bring headings for last view which is "Most Profitable" but unable to link it with the conditional formatting cell B7, which I am working, please let me know if you have any clue.

Excel help 11.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
2	User selection
3	View	Most Profitable
4	Number of Scenarios	3
5	Number of of Years	4
6	Business Model	3
7	Number of columns	5
8			Scenario 3	Scenario 1	Scenario 3	Scenario 4	Scenario 3	Scenario 4	Scenario 1	Scenario 1	Scenario 2	Scenario 4	Scenario 1	Scenario 1	Scenario 2	Scenario 4	Scenario 4	Scenario 1	Scenario 4	Scenario 1
9			Y3	Y3	Y3	Y3	Y3	Y4	Y4	Y1	Y1	Y4	Y1	Y4	Y2	Y1	Y1	Y4	Y1	Y1
10			CF	CF	SF	DF	DF	DF	DF	CF	SF	SF	SF	SF	CF	DF	SF	CF	CF	DF
11	Sales		2,060	2,000	2,050	1,900	2,040	1,890	2,000	1,200	1,210	2,345	1,100	1,290	1,110	1,030	1,040	1,234	1,050	1,000
12	Cost		281	222	300	456	657	555	900	123	140	1,290	110	345	170	120	145	343	160	111
13	GP		1,779	1,778	1,750	1,444	1,383	1,335	1,100	1,077	1,070	1,055	990	945	940	910	895	891	890	889
14	Expense		100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
15	NP		1,679	1,678	1,650	1,344	1,283	1,235	1,000	977	970	955	890	845	840	810	795	791	790	789
16			18	11	8	23	9	43	25	27	42	14	15	17	19	37	35	31	34	13
17			Scenario 1	Scenario 1	Scenario 1	Scenario 2	Scenario 2	Scenario 2	Scenario 3	Scenario 3	Scenario 3	Scenario 4	Scenario 4	Scenario 4	Scenario 1	Scenario 1	Scenario 1	Scenario 2	Scenario 2	Scenario 2
18			Y1	Y1	Y1	Y1	Y1	Y1	Y1	Y1	Y1	Y1	Y1	Y1	Y2	Y2	Y2	Y2	Y2	Y2
19			DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF	DF	SF	CF
20	Sales		1,000	1,100	1,200	1,010	1,210	900	1,020	1,111	789	1,030	1,040	1,050	1,060	1,070	1,080	1,090	1,100	1,110
21	Cost		111	110	123	230	140	980	333	500	800	120	145	160	180	900	876	765	875	170
22	GP		889	990	1,077	780	1,070	(80)	687	611	(11)	910	895	890	880	170	204	325	225	940
23	Expense		100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
24	NP		789	890	977	680	970	(180)	587	511	(111)	810	795	790	780	70	104	225	125	840
25	Rank		18	11	8	23	9	43	25	27	42	14	15	17	19	37	35	31	34	13
26
27			Scenario 3	Scenario 1	Scenario 3	Scenario 4	Scenario 3	Scenario 4	Scenario 1	Scenario 1	Scenario 2	Scenario 4	Scenario 1	Scenario 1	Scenario 2	Scenario 4	Scenario 4	Scenario 1	Scenario 4	Scenario 1
28			Y3	Y3	Y3	Y3	Y3	Y4	Y4	Y1	Y1	Y4	Y1	Y4	Y2	Y1	Y1	Y4	Y1	Y1
29			CF	CF	SF	DF	DF	DF	DF	CF	SF	SF	SF	SF	CF	DF	SF	CF	CF	DF
Summary (2)

Cell Formulas
Range	Formula
C8:T8	C8	=IF($B$3="Years by Scenario", IF(COLUMN(A2)<=$B$4*$B$5*$B$6,"Scenario "&MOD(INT((COLUMN(A2)-COLUMN($A$2))/$B$6),$B$4)+1, ""),IF($B$3="Scenario by years",IF(COLUMN(A2)<=$B$4*$B$5*$B$6,"Scenario "&CEILING((COLUMN(A2)-COLUMN($A$2)+1)/($B$5*$B$6),1),""),IF($B$3="Most Profitable", C27, "")))
C9:T9	C9	=IF($B$3="Scenario by years",IF(COLUMN(A2)<=$B$4*$B$5*$B$6,"Y"&MOD(INT((COLUMN(A2)-COLUMN($A$2))/$B$6),$B$5)+1,""),IF($B$3="Years by Scenario",IF(COLUMN(A2)<=$B$4*$B$5*$B$6,"Y"&CEILING((COLUMN(A2)-COLUMN($A$2)+1)/($B$4*$B$6),1),""),IF($B$3="Most Profitable",C28,"")))
C10:T10	C10	=IF($B$3="Scenario by years", IF(C9<>"", CHOOSE(MOD(COLUMN(A2)-COLUMN($A$2), $B$6)+1, "DF", "SF", "CF"), ""), IF($B$3="Years by Scenario", IF(C9<>"", CHOOSE(MOD(COLUMN(A2)-COLUMN($A$2), $B$6)+1, "DF", "SF", "CF")),IF($B$3="Most Profitable",C29,"")))
C11:T12,C14:T14	C11	=SUMIFS($C20:$AX20,$C$17:$AX$17,C$8,$C$18:$AX$18,C$9,$C$19:$AX$19,C$10)
C13:T13,C15:T15,C24:T24,C22:T22	C13	=C11-C12
C16:T16	C16	=+C25
C25:T25	C25	=RANK.EQ(C24,$C$24:$AX$24,0)
C27:T27	C27	=HLOOKUP(C$1,$C$16:$AX$19,2,0)
C28:T28	C28	=HLOOKUP(C$1,$C$16:$AX$19,3,0)
C29:T29	C29	=HLOOKUP(C$1,$C$16:$AX$19,4,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C8:UD8	Cell Value	>="Scenario 6"	text	NO
C8:UD8	Cell Value	="Scenario 5"	text	NO
C8:UD8	Cell Value	="Scenario 4"	text	NO
C8:UD8	Cell Value	="Scenario 3"	text	NO
C8:UD8	Cell Value	="Scenario 2"	text	NO
C8:UD8	Cell Value	="Scenario 1"	text	NO
C10:UD10	Cell Value	="CF"	text	NO
C10:UD10	Cell Value	="SF"	text	NO
C10:UD10	Cell Value	="DF"	text	NO
C9:UD9	Cell Value	>="Y6"	text	NO
C9:UD9	Cell Value	="Y5"	text	NO
C9:UD9	Cell Value	="Y4"	text	NO
C9:UD9	Cell Value	="Y3"	text	NO
C9:UD9	Cell Value	="Y2"	text	NO
C9:UD9	Cell Value	="Y1"	text	NO
C25:AX25	Other Type	Color scale		NO
C24:AX24	Cell Value	duplicates	text	NO
C15:V15	Other Type	Color scale		NO
B7	Expression	=B3<>"Most Profitable"	text	NO
B6	Expression	=B3="Most Profitable"	text	NO
B5	Expression	=B3="Most Profitable"	text	NO
B4:B6	Expression	=B3="Most Profitable"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B3	List	Scenario by years, Years by scenario, Most Profitable
B4	List	=IF($B$3="Most Profitable","",Control!D2:D21)
B5	List	=IF($B$3="Most Profitable","",Control!F2:F21)
B6	List	=IF($B$3="Most Profitable","",Control!H2:H4)
B7	List	=IF($B$3="Most Profitable",Control!I2:I49,"")