Batch Macro to Dynamically Select a Column in a Formula

[dylanflorig]

Hi all - 

I'm currently building a workflow in which I have several years of data running across columns. I then have an additional column with a formula set to equal one of the other columns depending on which year I want to look at. This data is then fed into an optimizer. I want to run the workflow multiple times to give me new optimized outputs for each year of variables.

If I were to do this manually, I could simply change the formula (in the attached that column is 'TargetYear') to equal the next year and re-run the entire workflow, and so on until I've optimized each individual year. Obviously this would be a very slow/manual process.

So my question is, how can I use a Batch Macro (or any other options that may be more efficient) to automate the process of selecting each year and sending it through the rest of the workflow? An additional wrinkle is that I need it to do this simultaneously for two sets of variables that will go into the O and B inputs of the Optimization tool.

Thanks!

[DavidP · Accepted solution]

Here is an example of doing it with a batch macro. Note that each row in the Control Input will cause the marco to execute the workflow once (using that control value), so the number of output rows is the number of input rows multiplied by the number of control rows.

If this is not what you want, please be a little more specific as to what you'd like as the output.