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.
Bookmarks