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

 2016
 Platform

 MacOS
Hello All,
I am working on a project that has several macros but there is one piece of the puzzle I am unable to completed. I will do my best to explain what I need visually.
I have a loop that looks at a sheet (Timing Assumptions) and based on a row called Percentages, the user is able to enter upto 23 variables into the range and I was hoping to systematically build a formula based on the users input. Currently the scenario never gets passed 4 variables but I am hoping to have code that can account for any scenario.
I have mocked up 3 scenarios so you can see how the formulas change based on the user inputs. The end result of the formula will be entered into a cell with the current loop that I have. Just need a way to create the formula.
I am hoping to have a Loop/Join created that will look at the Percentage row and scan the start position of the first used cell and offset(1,0) and define the number variable above and enter that into the formula within the EDATE($J14, Variable) and append to the formula for each percentage in the range.
Any help is much appreciated
I am working on a project that has several macros but there is one piece of the puzzle I am unable to completed. I will do my best to explain what I need visually.
I have a loop that looks at a sheet (Timing Assumptions) and based on a row called Percentages, the user is able to enter upto 23 variables into the range and I was hoping to systematically build a formula based on the users input. Currently the scenario never gets passed 4 variables but I am hoping to have code that can account for any scenario.
I have mocked up 3 scenarios so you can see how the formulas change based on the user inputs. The end result of the formula will be entered into a cell with the current loop that I have. Just need a way to create the formula.
I am hoping to have a Loop/Join created that will look at the Percentage row and scan the start position of the first used cell and offset(1,0) and define the number variable above and enter that into the formula within the EDATE($J14, Variable) and append to the formula for each percentage in the range.
Any help is much appreciated
Book1  

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  
2  Media  
3  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  
4  Percentage  50%  100%  
5  1 Variable  
6  Formula Result=  IFERROR(IF(T$4=EDATE($J14,1),$Q14*.50,"")),"")  
7  
8  Media  
9  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  
10  Percentage  30%  50%  10%  100%  
11  2 Variable  
12  Formula Result=  IFERROR(IF(T$4=EDATE($J14,1),$Q14*.30,IF(T$4=EDATE($J14,0),$Q14*.50,IF(T$4=EDATE($J14,1),$Q14*.10,""))),"")  
13  
14  Media  
15  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  
16  Percentage  30%  50%  10%  10%  100%  
17  4 Variable  
18  Formula Result=  IFERROR(IF(T$4=EDATE($J14,1),$Q14*MediaNeg1,IF(T$4=EDATE($J14,0),$Q14*MediaStart,IF(T$4=EDATE($J14,1),$Q14*MediaPlus1,IF(T$4=EDATE($J14,2),$Q14*MediaPlus2,"")))),"")  
Sheet1 