Johnny Thunder
Wellknown Member
 Joined
 Apr 9, 2010
 Messages
 673
 Office Version

 2016
 Platform

 MacOS
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:
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, Nov19.
2. J14 = Nov19
3. EDATE($J14,1) is a statement that takes a Date from J14 1 Month (Oct19)
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.
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, Nov19.
2. J14 = Nov19
3. EDATE($J14,1) is a statement that takes a Date from J14 1 Month (Oct19)
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  Jul19  Aug19  Sep19  Oct19  Nov19  Dec19  Jan20  Feb20  Mar20  
2  Name 1  Nov18  May19  Nov19  $ 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,"")))),"") 