# Add row and copy formula

Occasional Contributor

# Add row and copy formula

Hello, I wonder how I add an entire row and take the formulas from below. And also, I'm adding a row at the top side, and I have 1 parameter that calculates the las 13 data. By that I need the oldest data that don't be calculated and the new one to be calculated. The oldest that is at the bottom. Thanks.

10 Replies

# Re: Add row and copy formula

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

# Re: Add row and copy formula

Here is a shar link that can be edited https://1drv.ms/x/s!As3uG0-FXb_-h0oL_c61aqxD7fmO

As you can see I add a row witch now is row number 7. What is now row number 8 was previously row 7. I will continue to add row every week like this one and the rows will continue to go down. In row 6 it will continue to be in its place, but I need that row to measure the sum of the ls 13 weeks only. In this case what is now row 20 can't be measured. This row was previous row 19 and had to be measured. When I added a row none of the formula I needed for that row were copied to this new row, and also it was not added for the total sum for row 6. Thanks for your time. @Hans Vogelaar

# Re: Add row and copy formula

If you are inputting basic data, this is best done using an Excel table.  To insert values at the top requires you to insert a row to the sheet, but any formulas should extend to include the new row.  Using 365 a formula to return the sum of the top 13 items of a value field would be

``````= SUM(
TAKE(Table1[value],13)
)``````

Similar things can be done using INDEX:INDEX

# Re: Add row and copy formula

An old-fashioned formula in B6:

=SUM(OFFSET(B6,1,0,13,1))

Copy to the other cells where you need a similar formula.

You might use the following macro to insert a new row.

``````Sub InsertNewRow()
Range("A7:O7").Copy
Range("A7:O7").Insert Shift:=xlShiftDown
Range("A7:O7").SpecialCells(xlCellTypeConstants).ClearContents
End Sub``````

Warning: some of the formulas will return #DIV/0! until you enter data in the new row. To avoid this, you might change a formula such as

=B7/D7

to

=IFERROR(B7/D7,"")

or

=IFERROR(B7/D7,0)

# Re: Add row and copy formula

But were do you input that that you send in the box, can you edit that in the link I send you so I can see it, you can edit it. Thanks

# Re: Add row and copy formula

See the attached version, It is now a macro-enabled workbook, so you will have to allow macros when you open it.

# Re: Add row and copy formula

This is your data converted to a Table.  The yellow cells contain the formulae

``````= SUM( TAKE(Table1[LongBI], 13) )

= SUM( OFFSET(Table1[LongCH],,,13) )``````

# Re: Add row and copy formula

Thanks @Peter Bartholomew

Thanks