I want to control a column of formulas by being able to change the core formula in one cell at top

Question

I want to be able to change the formula in once specific cell, and have it apply to an entire column. For a simple example in F1 I'll have =IF(A1>100,A1+5,A1+10).

Now I want column B to calculate the same way as F1 (lets call it the controlled cell). So B5 would be =IF(A5>100,A5+5,A5+10) and B6 would do the same for A6 value etc down each row.

I want it so all I have to do is change the cell F1, and all of B1's formula will change as well..

I know a few was to do this like instead of +5 and +10 I can put +F1 and F2 and just change those, however the real formula is a bit more complex so I would need quite a bunch of cells todo this for each of maybe 50 of these Im doing which will take up hundreds of cells and make the concept of an easy change pointless.

Just using my imagination but is there a method where I can set some kind of variable/wild card in F1 like =IF(VARIABLE>100,VAIRABLE+5,VARIABLE+10). Have B1 use F1 as the formula and then the variable = A1, and in next row use A2, then A3 etc.. That way when I want to change the entire columns formula I can just change F1 to IF(VARIABLE>100,VARIABLE+13,VARIABLE+23) and all of B will use the new formula?

In short I want to control a column of formulas by being able to change the core formula in one cell at top.

Current 365 Excel (2020 version 2202) 64 bit

Answer 1

What about using the "Format as Table" functionality? You could specify a formula in the first row of the table and it would automatically be applied to the whole column. No need for a seperate "control cell".

Otherwise there's no way around a VBA macro: Worksheet_Change -> Control cell affected? -> Copy formula to controlled cells