How to use formulas/functions or VBA code to complete the following dynamic tasks

[NotExcelWizard]

I am trying to use either VBA code or formulas/functions to calculate values highlighted yellow based on the criteria above. I want my calculations to be dynamic such that when an input is changed the output is also changed. E.g. if for the first item, if one were to change gross profit to Revenue then the amount would update accordingly. In short, I want my answers to dynamically update when the input criteria are changed.

 

[PeteWright]

MrExcel_NotExcelWizard_DynFormulas.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		1/2015	3/2015	6/2015	7/2015	12/2015	1/2016	2/2016	3/2016	4/2016	5/2016	6/2016	7/2016	8/2016
2		Jan	Mar	Jun	Jul	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
3		2015	2015	2015	2015	2015	2016	2016	2016	2016	2016	2016	2016	2016
4	Revenue	1'223'290	1'336'951	2'134'749	1'282'596	1'023'852	2'431'235	2'442'758	1'906'217	1'227'156	1'171'901	2'905'403	2'292'887	2'998'992
5	Cost of Goods Sold	160'597	866'890	619'361	710'113	116'087	258'490	326'205	582'539	233'607	679'293	526'047	104'657	619'706
6	Gross Profit	1'062'693	470'061	1'515'388	572'483	907'765	2'172'745	2'116'553	1'323'678	993'549	492'608	2'379'356	2'188'230	2'379'286
7	Gross Profit %	87%	35%	71%	45%	89%	89%	87%	69%	81%	42%	82%	95%	79%
8	Operating Costs	95'541	70'162	85'770	73'203	50'391	58'111	72'147	84'864	82'086	93'270	63'655	65'580	79'396
9	Net Income	967'152	399'899	1'429'618	499'280	857'374	2'114'634	2'044'406	1'238'814	911'463	399'338	2'315'701	2'122'650	2'299'890
10
11	Line Item	Revenue
12	Month	Jan
13	Year	2015
14	Amount	1'223'290
15
16	Net Income Over	1000000
17	Year	2015
18	# of Instances	7
19
20	Year	2015
21	Net Income	4153323
Sheet11

Cell Formulas
Range	Formula
B1:N1	B1	= DATEVALUE("01/"&B2&"/"&B3)
B4:N4	B4	= RANDBETWEEN(1000000,3000000)
B5:N5	B5	= RANDBETWEEN(100000,1000000)
B6:N6	B6	= B4-B5
B7:N7	B7	= B6/B4
B8:N8	B8	= RANDBETWEEN(50000,100000)
B9:N9	B9	= B6-B8
B14	B14	=OFFSET(A3,MATCH(B11,A4:A9,0),XMATCH(DATE(B13,IFS(B12="Jan",1,B12="Feb",2,B12="Mar",3,B12="Apr",4,B12="May",5,B12="Jun",6,B12="Jul",7,B12="Aug",8,B12="Sep",9,B12="Oct",10,B12="Nov",11,B12="Dec",12),1),B1:N1,0,1))
B18	B18	= SUMPRODUCT( --(B9:N9>B16)*1)
B21	B21	= SUMIFS(B9:N9,B3:N3,"="&B20)

Cells with Data Validation
Cell	Allow	Criteria
B16:C16	Whole number	between 1 and 100000000
B17:C17	Whole number	between 1900 and 2100
B20:C20	Whole number	between 1900 and 2100
B11:C11	List	=$A$4:$A$9
B12:C12	List	Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec
B13:C13	Whole number	between 1900 and 2100

You can download the sample Workbook from my OneDrive: MrExcel_NotExcelWizard_DynamicFormulas.xlsx

 

[SunnyAlv]

@PeteWright I think you have some mistakes like in Table 2, you should only count the year 2015, not all years, also in the third table


maybe like this

Cell B14

=INDEX($B$4:$N$9,MATCH($B$11,$A$4:$A$9,0),MATCH(1,($B$2:$N$2=$B$12)*($B$3:$N$3=$B$13),0))

Cell B18

=SUMPRODUCT(--(INDEX($B$4:$N$9,MATCH("Net Income",$A$4:$A$9,0),SEQUENCE(COUNTIF($B$3:$N$3,B17)))>1000000))

Cell B21

=SUM(INDEX($B$4:$N$9,MATCH("Net Income",$A$4:$A$9,0),SEQUENCE(COUNTIF($B$3:$N$3,B17))))

 

[kevin9999]

Another option:

MrExcel_NotExcelWizard_DynamicFormulas.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2		Jan	Mar	Jun	Jul	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
3		2015	2015	2015	2015	2015	2016	2016	2016	2016	2016	2016	2016	2016
4	Revenue	1,189,240	1,730,587	2,162,513	2,276,642	2,328,286	1,961,649	1,543,312	1,730,430	1,688,234	1,821,176	2,711,676	2,968,454	2,471,696
5	Cost of Goods Sold	191,926	635,782	355,663	191,592	401,608	782,278	463,559	553,622	475,912	340,533	103,483	927,065	920,504
6	Gross Profit	997,314	1,094,805	1,806,850	2,085,050	1,926,678	1,179,371	1,079,753	1,176,808	1,212,322	1,480,643	2,608,193	2,041,389	1,551,192
7	Gross Profit %	84%	63%	84%	92%	83%	60%	70%	68%	72%	81%	96%	69%	63%
8	Operating Costs	95,389	72,609	86,660	71,431	52,644	74,612	91,609	85,914	63,244	81,536	58,507	59,163	82,463
9	Net Income	901,925	1,022,196	1,720,190	2,013,619	1,874,034	1,104,759	988,144	1,090,894	1,149,078	1,399,107	2,549,686	1,982,226	1,468,729
10
11	Line Item	Revenue
12	Month	Mar
13	Year	2015
14	Amount	1,730,587
15
16	Net Income Over	1,000,000
17	Year	2015
18	# of Instances	4
19
20	Year	2015
21	Net Income	7,531,964
Sheet11

Cell Formulas
Range	Formula
B14	B14	=SUMPRODUCT((B3:N3=B13)*(B2:N2=B12)*(A4:A9=B11),B4:N9)
B18	B18	=COUNTIFS(B3:N3,B17,B9:N9,">"&B16)
B21	B21	= SUMIF(B3:N3,B20,B9:N9)

Cells with Data Validation
Cell	Allow	Criteria
B16:C16	Whole number	between 1 and 100000000
B17:C17	Whole number	between 1900 and 2100
B20:C20	Whole number	between 1900 and 2100
B11:C11	List	=$A$4:$A$9
B12:C12	List	Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec
B13:C13	Whole number	between 1900 and 2100