Build formula based off value in another Cell

[PeterWilson80]

I am trying to get a formula to build based off a row reference i put into another

I have tried to put a simplified example of what i mean below

E2 is calculated using the formula shown in F2 as an example

So i have 4 columns of data, A - D, and i want to, for the example, minus the fig in D2 from A2, but what i want to do is have the Column Letter pulled from the Cells E1 & F1, so if i changed those entries it changes the formula, so if i wanted to minus the fig in B2 from A2 i would change the D in cell F1 to a B.

Not sure if this is possible or how to do it.[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]=A2-D2[/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

You could use =INDIRECT(E1&"2")-INDIRECT("F1"&2)

Which is volatile, or you could use
=INDEX($A2:$D2,1, Match(E1,{"A","B","C","D"},0)) - INDEX($A$2:$D2, 1, FIND(F1,"ABCD"))
which is non volatile and uses two different methods of finding the column reference.

 

[Fluff]

Hi & welcome to MrExcel.
How abot
=INDIRECT($E$1&ROW(A2))-INDIRECT($F$1&ROW(A2))

 

[PeterWilson80]

I managed to get your example of =INDIRECT(E1&"2")-INDIRECT("F1"&2) and adjust it to what i required.

Thank you to both for the amazingly fast responses. Incredible

 

[Fluff]

Glad we could help & thanks for the feedback