I have a formula in cell A1 as =A2+A3, now i need the same formula in cell B1 as well however i dont want to copy paste the formula, i just want to give reference of A1 in B1 and B1 should calculate B2+B3 rather than A2+A3, the reason i want to do this if I change the formula in A1 it should take effect in B1 as well. Not sure if this is possible. Similarly, if the formula contains reference to any of the cells above below the formula cell the reference in B1 should dynamically change?

@Franco, actually I dont want to drag the formula, If I do that B1 will take the formula from A1 and change to B2+B3. In this case suppose I change the formula in A1 to =IF(A4="yes",B2+B3), this won't get applied to B1 unless I again drag the new formula. What I need is to change in one place and wherever there is a reference to this formula it should auto change with dynamic cell reference. So basically, I want to give reference to a formula wherever I want to use it and then create a repository of formulas to maintain, so any change I just do in the repository and the change takes effect wherever I give reference?

  • The 1st sentence is easy (see Franco's answer). Having the filled cells change dynamically when the initial cell changes is more complicated. If you are working with lists, and all the cells in a column have the same formula, you can use a table, which will update the formulas in the column when you change one of those cells. Otherwise, you would need VBA to detect the change and then copy the new formula. – fixer1234 Aug 13 '15 at 17:56

Yes it is possible to Fill the Formula.

  1. Select cell A1 (Where your formula is)
  2. Click and hold the little black square on the lower right corner (Your cursor should become a cross)
  3. Drag it to the right until last cell you need.

What I understand is that you want all the formulas to update?

As if A1 becomes "A2-A3", B1 should also change to "B2-B3".

The only way I know that a formula gets automatically updated through out a range is when used in a Table Object and that only works in a vertical arrangement of formulas.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.