Create formula based on cell precedent

[ffourie]

Hi

I would like to do the following for a number of different columns:

GP1 = F1​

GQ1 = (GP1 - 1)*D1​

Column GP is far removed from column F and therefore I re-display value.
Column "F" is not constant, but column "D" will always be offset -2 relative to F.

How can I do this without having to scroll to the left twice, first to find column F and then to find column D? Can I somehow use the precedent of GP to determine what the new "D" should be?

In the same worksheet it might also appear as:

GU80 = BW80​

GV80 = (GU80 - 1)*BU80​

Once again, BU80 is offset (0,-2) relative to GU80.

If possible, I would prefer to do this using a UDF because it might make dragging the formula to other cells easier, I think?

Thanks

 

[Aladin Akyurek]

Does this help?

=(F1-1)*OFFSET(F1,0,-2,1)

 

[ffourie]

Thanks Aladin, but not what I was looking for because I do not want to manually adapt the formula every time.

Wherever I use the proposed function (cell XFD1) it should look at the cell to the left thereof (cell XFC1), ask what cell was used to populate XFC1 (suppose it was F1), and then offset -2 with regard to the latter (to obtain D1).