Add row and copy formula

Occasional Contributor

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

@Nely1 

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?

Thanks for your reply, Hans.

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 

@Nely1 

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

@Nely1 

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)

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

@Nely1 

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

@Nely1 

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

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

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

Thanks @Peter Bartholomew 

Thanks