Dynamically Reference Columns in Formula

[willcogs26]

Hello! 

I am having issues automating my process. I currently have a process that pulls a historical YXDB and adds current month data to it, that is all automated and good to go. My issue now comes in with my calculations, as with a new month, I need to manually add the next month. 

So for example; my data is structured like this:

I have a formula tool right now that does this; 

This is where my issue comes in, being May, I now need to go in and update the process for a P4 Out_of _Stock

I'd like to have a multi-row formula do something like this:

Where if the current Field Name contains "Out_of_Stock" go find the field with the same Months Label; P1 for example, and divide by that field. 
However, as I am posting this, that formula doesn't work. I know the above formula would not work once I got to months 10 and 11, but for the sake of seeing if this is even possible, let's assume months end at 9 🙂 

This may not be possible in the way I have my data structured, but I thought if anyone could help it would be the community!

I have attached the workbook with the above tools in it!

Thanks!

[fmvizcaino · Accepted solution]

Hi @willcogs26 ,

Unfortunatelly there is no easy way of creating formulas like you want to (similar to indirec in excel).

But using cross-tab and transpose function, we are able to achieve exactly what you want 🙂

Best,

Fernando Vizcaino

[patrick_digan]

@willcogs26 First, @fmvizcaino's solution is a good one and the way I would do it as well. Second, I have an idea of adding an eval function that would be perfect for this situation. Third, I just wanted to add that the dynamic replace is another option for a use case like yours:

[willcogs26]

This works great! Thank you!