Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Copying formula to the next row when inserting a new row

Tags:

I have a row in which there are formulas using values of the same row. The next row is empty, just with a different background color.

Now, if I insert a new row (by right-clicking on the empty row and "insert"), I get a new row with NO background color (which is what I want), but the line also does NOT contain any formulas: how do I get Excel to be smarter and copying the formulas from the previous row, when creating the new row?

One more piece of information: data validation information (i.e. a drop down list) is copied when inserting the new row.

Thanks.

like image 808
Manu Avatar asked Dec 12 '12 22:12

Manu


2 Answers

Make the area with your data and formulas a Table:

enter image description here

Then adding new information in the next line will copy all formulas in that table for the new line. Data validation will also be applied for the new row as it was for the whole column. This is indeed Excel being smarter with your data.

NO VBA required...

like image 62
K_B Avatar answered Nov 20 '22 00:11

K_B


You need to insert the new row and then copy from the source row to the newly inserted row. Excel allows you to paste special just formulas. So in Excel:

  • Insert the new row
  • Copy the source row
  • Select the newly created target row, right click and paste special
  • Paste as formulas

VBA if required with Rows("1:1") being source and Rows("2:2") being target:

Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("2:2").Clear

Rows("1:1").Copy
Rows("2:2").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone
like image 40
InContext Avatar answered Nov 20 '22 00:11

InContext