Dynamically referecing columns in formulas

[AmineB]

Hello,

My first post here so I apologize in advance if broke Labels/Posting Rules.

I have a column (named Field) that contains other column names. I want to dynamically reference said columns in a formula.

I tried using double brackets: [[Field]] but that won't work.

Is there a special syntax to bypass this ?

[ydmuley]

Do you have a sample example? @AmineB 

[AmineB]

Hello,

I'm not allowed to upload anything so I'll try my best to retranscribe the table.

Col A	Col B	Col C	Field	Value
1	10	100	Col A	Yes
2	20	200	Col B	Yes
3	30	300	Col C	Yes
1	10	100	Col A	No
2	20	200	Col B	No
3	30	300	Col C	No

Basically, I want to do a formula :

IF [Value] = "Yes" THEN

         IF [[Field]] = 10 THEN "Message 1" ELSE "Message 2" ENDIF

ELSE

         IF [[Field]] = 10 THEN "Message 3" ELSE "Message 4" ENDIF

ENDIF

[ydmuley]

It is still difficult to comprehend your expected output or the formula that you have written.

I have written down something like this, is this what you are looking for?

If not, instead of writing the fomula, please just tell me the expected output (dont write formula) just tell what you are looking to find within the data set.

[AmineB]

Thank you for your patience. 

The condition is based on the Field column which contains in my dataset +130 different variables (each variable is named after an already existing column)

In the example I made I can make a switch statement for 3 different values. But for +130 it's more complicated.  I'll try to make a different example:

COL 1	COL 2	COL 3	COL 4	COL 5	COL 6	Field
1		34	66		98	COL 1
1		34	66		98	COL 2
1		34	66		98	COL 3
1		34	66		98	COL 4
1		34	66		98	COL 5
1		34	66		98	COL 6

My goal is to check whether each one is empty or not, I apply a formula  :

IF IsEmpty([[Field]]) THEN "Yes" ELSE "No" ENDIF

I was hoping that [Field] would be read as COL 1 and the I have the second brackets so it be treated as [COL N]

To get something like this: 

COL 1	COL 2	COL 3	COL 4	COL 5	COL 6	Field	Result
1		34	66		98	COL 1	No
1		34	66		98	COL 2	Yes
1		34	66		98	COL 3	No
1		34	66		98	COL 4	No
1		34	66		98	COL 5	Yes
1		34	66		98	COL 6	No

The ultimate goal is to have for each row tracking each column whether it's empty or not. 

[patrick_digan · Accepted solution]

@AmineB Out of the Box the Dynamic Replace is the tool to use for something like this. I've attached an example. I believe there is a Crew Macro if you use those. I also have an idea out there of adding an eval function which would make this much more straightforward.

[AmineB]

@ patrick_digan Hello, thank you for the reply.  

I agree with you, I believe I'm looking for something similar to the eval function. Unlucky it's not yet implemented. 

For security reasons, I can't use macros. 

I'm checking out the Dynamic Replace and it seems the right track for me. So thank you.

Have a good one 🙂