Automatically copy formula into adjacent cell when a new column is inserted

[Chinoxl]

Hi everyone,

I am trying to get excel to automatically copy and insert formulas from one row into its adjacent new cells when a new column is inserted. I have turned ranges into tables and this only seems to work for rows but not columns, I explain my specific problem in more details below.

In the excel worksheet (attached with random names and data), I have created a table across a range of cells and inserted row() functions into the cells in Column C (for serial numbers). This is so that each time I insert a new row, the serial number automatically updates. For example: If I insert a new row to appear after serial number 12 (row 16), the table automatically updates the new row serial number to 13 (row 17).

excelA.JPG

However, each time I insert a new column to include a new judge, the total row just above the bottom of the new column doesn’t update with the formula from its adjacent cell on the left.
Example: I want to add information for a new Judge, say Judge 10, so I insert a new column to appear after N (a new “O” column). The conditional formatting copies to the new column’s cells adjacent to the blue rows, but the formula for the row I use for totals(highlighted in image below) doesn’t copy to the empty adjacent cell in the just added new column.

excelB.JPG

How do I fix this? So that each time I add a new judge (new column), the formula from the total row automatically appears in the adjacent cell of the new column as highlighted?

Also, if this can be done with a macro I would really appreciate this.

Thanks.

p.s: I'm a newcomer to the forum please forgive my scrappy images.

[oeldere]

O5 =SOM($F5:INDIRECT(ADRES(Row(),Column()-1))) and drag down.

[MRUTOR]

Hello,

here a VBA solution. But here you should not change the position of the table. Else code needs to be amended regarding the cell position.

[Chinoxl]

O5 =SOM($F5:INDIRECT(ADRES(Row(),Column()-1))) and drag down.

Edited: I have just realised what you were referring to in your comment and how important it is. The formula in the totals column doesn't update to include the new cell when a new column is inserted but "The formula you provided" inserted into the totals column does this perfectly! This is different from what I was originally asking for (which MRUTOR has helped with), but thanks to you for noticing this as I would not have guessed the SUM formula would not automatically take this in the totals column without =SUM($F5:INDIRECT(ADDRESS(Row(),Column()-1))), this has really saved me from calculation errors, thanks alot Oeldere.

[Chinoxl]

Hello,

here a VBA solution. But here you should not change the position of the table. Else code needs to be amended regarding the cell position.

You are a genuis! Exactly what I was looking for! Thanks alot!!!!!

[MRUTOR]

You are welcome. Thanks for the compliment.

[oeldere]

@Chinoxl

Add this formula in O5.

After that add a new column.,

Imput a value in the new column.

See what will happen in (new cell) P2 (the value will change).


Please reply your comment after you have tried that.

[Chinoxl]

Thanks Oeldere, I have edited my previous comment.

[oeldere]

Thanks for the rep.

Glad I could help.

Thanks for marking the question solved.