VBA Help - Creating a Formula with x amount of variables based on Counter

[Johnny Thunder]

Hello All,

I have a range that has a counter in it that goes from -11 to + 11 and some percentages below. This then feeds into a formula on another sheet that currently is manually created. I am trying to have a script that will scan if a percentage has been entered and based on the value above it, update the formula with however many variables are needed.

Example:

Media Scripted & Kids
Periods	-11	-10	-9	-8	-7	-6	-5	-4	-3	-2	-1	0	1	2	3	4	5	6	7	8	9	10	11	Totals
Percentage											30%	50%	10%	10%										100%

The formula for this line looks like this: =IFERROR(IF(AS$4=EDATE($J14,-1),$Q14*MediaNeg1,IF(AS$4=EDATE($J14,0),$Q14*MediaStart,IF(AS$4=EDATE($J14,1),$Q14*MediaPlus1,IF(AS$4=EDATE($J14,2),$Q14*MediaPlus2,"")))),"")

Explanation:
1. AS$4 is a Date, Nov-19.
2. J14 = Nov-19
3. EDATE($J14,-1) is a statement that takes a Date from J14 -1 Month (Oct-19)
4. Q14 * MediaNeg1 (Q14 is a Dollar Amount $1000), MediaNeg1 is a Named Range to the table above looking at the -1 percentage which is 30%. Because this was the first variable in the table from above that is why it was entered first.
4a. The EDATE Part of the formula is needed because it is taking a Date and applying some +1 Month or -1 Month to the value.
5. IF(AS$4=$J14,$Q14*MediaStart - Is the same as the above using the next variable from the Table above in position 0 which is the 50%. I think if I am able to do this with VBA, I would just put the percentages instead of the Named ranges like MediaStart.
6. Exact same as above just the next variable from the table.

Hopefully you can see what the formula is doing now. What I need is a way to systematically build the formula from the first variable in the table to the last which just appends to the formula based on how many Percentages are entered into the table.

Here is a mock up with the above using different ranges so you can see how the formula works and looks when completed.

Marketing Model - Proposal Working File v2.5.xlsm
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Name of Project	Idea Date	Production	Completed	Amt		Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20	Mar-20
2	Name 1	Nov-18	May-19	Nov-19	$ 3,000					900	1500	300	300
Sheet1

Cell Formulas
Range	Formula
M2:U2	M2	=IFERROR(IF(M$1=EDATE($J2,-1),$K2*MediaNeg1,IF(M$1=EDATE($J2,0),$K2*MediaStart,IF(M$1=EDATE($J2,1),$K2*MediaPlus1,IF(M$1=EDATE($J2,2),$K2*MediaPlus2,"")))),"")