Dynamically update formula somehow?

Question

Hello,

I have applied a Vlookup between 2 files and as expected, I have received right_values and normal values.

Now, right_values need to be populated as it is into other columns based on the name of the column. (Like using formula to assign value from 1 column to other)

The column names are like: Jan'24 Value      Feb'24 Value     Mar'24 Value and so on... until December

All the columns are already created for an year in the template

And the column to be updated is simply the corresponding column at which the workflow is run. So, if run in February, the column to be updated will be Feb'24 Value.

For an year, it can be done by adding a column in the same format, named "CurrentMonthYear" using date time now and IF [CurrentMonthYear] == "Jan'24" THEN [Jan'24 Values] = [Right_Values] ELSEIF [CurrentMonthYear] == "Feb'24" THEN [Feb'24 Values] = [Right_Values] and so on...

But this will hold true only this year, as next year, the formula needs to be updated by the business team to accommodate for 2025 (replacing all 24 with 25).

So, my question is...

Is there any way to automatically update all the 24s in the formulas to 25 (year in which workflow is running) so that the workflow runs perfectly fine without any further updates in future?

apathetichell · Answer

There are ways to do this with Dynamic Replace - but tbh - that's now how you should be solving this. You should be transposing your data so your column name is always [Name] - and working off of that... basically you can then do an  join with your datastream with the updated value and proper month and then union back in the rest of the data after updating the relevant month. After union - crosstab (and probably dynamic rename with your original names)

Gawa · Answer

hi @ShantanuDagar

I guess it is difficult to update formula expression, especially modify the inside of column name like  [Jan'24 Values].

As a workaround, I suggest to change column name before Formula tool like this. By doing this, formula expression can be simpler regardless of when it executes.

Overall WF

Assume input data looks like this

First, erase YEAR information from column

Then, you can write simple formula expression like this. Even if year changes, data structure (Jan, Feb, Mar...total 12 months) should not change in your dataset, I suppose.

After calculation, you can revert to the original column name, as required.

1236358_gawa.yxmd

Qiu · Answer

@ShantanuDagar

Can you share your sample input and output?