Forum Discussion
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?
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. HansVogelaar
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 SubWarning: 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
See the attached version, It is now a macro-enabled workbook, so you will have to allow macros when you open it.