3

I have the following formula in cell A1 where I am simply adding and subtracting some numbers...

  =B1+D1+F1-C1-E1-G1

And I repeat this going down to row 100 such that the formula in A2 is...

  =B2+D2+F2-C2-E2-G2

And so on. Then I run a macro that inserts two columns in front of column B and places two new numbers in what is now the new columns B and C, with the other six columns moved to the right. But I want the formula in A1 to reference the same columns as before, not follow the original columns. How can I do this and also be able to drag the formula from A1 down to A100?

CharlieRB
  • 22,754
D Smith
  • 31
  • 1
  • 1
  • 2

2 Answers2

2

You can use function offset.

See excel help for better uderstanding

Fill this to A1 and drag down.

=OFFSET(A1;0;2)+OFFSET(A1;0;4)+OFFSET(A1;0;6)-OFFSET(A1;0;3)-OFFSET(A1;0;5)-OFFSET(A1;0;7)

Update

Sorry, I have non-English version of the Excel where ; are as separation character in formulas.

In English Excel is it probably with ,

=OFFSET(A1,0,2)+OFFSET(A1,0,4)+OFFSET(A1,0,6)-OFFSET(A1,0,3)-OFFSET(A1,0,5)-OFFSET(A1,0,7)
Lluser
  • 356
1

Use named formulas. Simply do this:

  1. Select Cell A1.
  2. On Formulas go to Define Name
  3. In the dialog put in the Name box the name that you want to use. For example: my_formula.
  4. In the Refers to box, write: =B1+D1+F1-C1-E1-G1 and click OK.
  5. On A1 write =my_formula, check if the result is correct and then copy to A2, A3, A4..... A100.

Now, it doesn't matter if you add columns or remove columns.

If in the future you need to modify the formula go to Formulas -> Name Manager.

jcbermu
  • 17,526
  • Note that when doing this, if you enter the formula by clicking the cells instead of typing in their addresses manually, you will need to remove the $ signs from the addresses Excel generate, otherwise the named formula will shift when you insert columns. – GSerg Dec 28 '18 at 09:15