I don't understand how can I autofill a formula that is on a cell to a new column added on the left. Let's make an example: [Example](https://i.stack.imgur.com/ufCvH.png) I have on cell "B2" a date that is "B1 180", when I add a new column on the left of "B2", the new "B2" does not contain the formula. How can it autofill the formula when adding new column on the left?

Thanks.

I'm expecting that when I add a new column on the left of "B2", the new B2 will autofill the formula "B1 180".

CodePudding user response：

Could you copy and insert the existing column? That would copy/paste the formulas with it as long as your references were static, e.g. $A$1, not A1. If you need the formula to be pulled down over rows you can use $A1, but if the column reference is dynamic you'll end up looking to the wrong cell or creating a #REF error.

This can be achieved more easily with inserted rows by formatting the range as a table, but insert should as described above.

CodePudding user response：

I don't think Excel Autofill works automatically when creating a new column. What you could do is better write your formula with absolute or semi-absolute reference to B1 ($B1, B$1 or $B$1), create new columns and then use the "black plus" to extend your formula by Autofill. Another way could be to copy-paste the entire B column onto the left of it self: if your formula is well-written, it'd word also.

CodePudding user response：

As commented in aclayden's answer you can do this with VBA

Using a worksheet change event we can watch a cell for any changes and if there is a change, add the formula.

Right click on the Sheet tab, then click View Code.

In the VBAproject box you should see the sheet you want to be watching, if it is not the correct sheet, double click on the sheet's name that you want.

You should now have the area where you can paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim ws As Worksheet
Set ws = Worksheets("Sheet1") ' set the target worksheet name here

If Not Application.Intersect(ws.Range("B1"), Range(Target.Address)) Is Nothing Then  ' set the cell to be watched
        
    ws.Range("B2").FormulaR1C1 = "=R[-1]C 180" ' set the cell where you want the formula - The formula will need changing if you want to refrence another cell.

End If

End Sub

Make sure you change sheet names and cell references in the code to match your reequipments, if you change cell references then you will need to change the formula.

You can now go back to the sheet and add a new column, this should now add the formula automatically.