I currently have 28 distribution tables on 28 different worksheets in the same workbook. Im trying to get the following formula into my VBA code on the 29th (summary) worksheet - either into one cell that i can then fill across over a variable amount of months, or entered into that variable amount of cells (months) by column all at the same time, (using With... RANGE.RESIZE) whichever is easier and doable.
Essentially, the user of this workbook will look at the 28 pictoral graphs that accompany the 28 different distributions, they will select the one that most appropriately depicts the curve that their cost profile will follow, and based on the amount of months for their effort, the formula will distribute a variable Total Cost value by month among the variable amount of months. This formula was successful when pasted into each cell in excel, but i'd like to update the workbook for the task to be done with VBA.
=$C29*VLOOKUP($B29,INDIRECT("curve"&$D29,1),E$28+1,FALSE)
Where:
C29 is the total cost variable that i am distributing among the variable months
B29 is the amount of variable months
D29 is my indirect reference for the variable curve selected (between 1 and 28 options)
E28 is equal to 1 --> AN28 is equal to 36 representing the max amount of months the distribution curves will return a value for before summing to 1.0 in total
Again, once entered into the first cell, i would need to be able to fill right by 'x' amount of variable months and the column returned by the lookup would have to start in column 2 (E28+1) and work right to grow to a maximum of 36 months (37th column) which is as much as my tables will allow for.
-VBA newbie
Essentially, the user of this workbook will look at the 28 pictoral graphs that accompany the 28 different distributions, they will select the one that most appropriately depicts the curve that their cost profile will follow, and based on the amount of months for their effort, the formula will distribute a variable Total Cost value by month among the variable amount of months. This formula was successful when pasted into each cell in excel, but i'd like to update the workbook for the task to be done with VBA.
=$C29*VLOOKUP($B29,INDIRECT("curve"&$D29,1),E$28+1,FALSE)
Where:
C29 is the total cost variable that i am distributing among the variable months
B29 is the amount of variable months
D29 is my indirect reference for the variable curve selected (between 1 and 28 options)
E28 is equal to 1 --> AN28 is equal to 36 representing the max amount of months the distribution curves will return a value for before summing to 1.0 in total
Again, once entered into the first cell, i would need to be able to fill right by 'x' amount of variable months and the column returned by the lookup would have to start in column 2 (E28+1) and work right to grow to a maximum of 36 months (37th column) which is as much as my tables will allow for.
-VBA newbie
