This page is simply an extended example of descriptive information associated with the INSRTROW macro.
Example of an Excel Table A B C D E 1 Description Count Unit Price Amount Running Total 2 Article-1 3 2.50 7.50 7.50 3 Article-2 2 3.50 7.00 14.50 4 Article-3 33 0.50 16.50 31.00 5 Article-4 5 2.95 14.75 45.75 6 Total 45.75 7 The above data view might appear as follows in the formula view. By coding in this fashion the use of the INSRTROW macro greatly simplifies the insertion of new rows.
A B C D E 1 Description Count Unit Price Amount Running Total 2 Article-1 3 2.50 =B2*C2 =d2 3 Article-2 2 3.50 =B3*C3 =offset(E3,-1,0)+D3 4 Article-3 33 0.50 =B4*C4 =offset(E4,-1,0)+D4 5 Article-4 5 2.95 =B5*C5 =offset(E5,-1,0)+D5 6 Total =SUM(D2:offset(D7,-1,0) Why must we use that funning looking OFFSET Worksheet Function
OFFSET(reference, rows, cols, height, width)
In order to use the InsertRowsAndFillFormulas macro (INSRTROW macro), the formulas must be entered naming only the cellnames found on the current row. By referencing cells in the current row any insertion or deletion of lines will cause formulas to be updated. In order to refer to cells elsewhere but to name only cell addresses in the current row. See example above using OFFSET.
How to get into trouble inserting lines using simple formulas
A simplified version of the above would appear as follows. I will be describing the problems with this simplified version below.
Simplified Example (avoid this form) A B C D E 1 Description Count Unit Price Amount Running Total 2 Article-1 3 2.50 =B2*C2 =d2 3 Article-2 2 3.50 =B3*C3 =E2+D3 4 Article-3 33 0.50 =B4*C4 =E3+D4 5 Article-4 5 2.95 =B5*C5 =E4+D5 6 (leave-blank) 7 Total =SUM(D2:D6) Leaving a blank Row 6 allowed for easy insertion of a row without having to change the SUM formula.
In the next example the blank Row 6 will be removed and the SUM formula will use OFFSET to simply insertion of lines.
The above can be coded simply as follows:
A B C D E 1 Description Count Unit Price Amount Running Total 2 Article-1 3 2.50 =B2*C2 =d2 3 Article-2 2 3.50 =B3*C3 =E2+D3 4 Article-3 33 0.50 =B4*C4 =E3+D4 5 Article-4 5 2.95 =B5*C5 =E4+D5 6 Total =SUM(D2:offset(D6,-1,0) 7 In the above example if a row were inserted the SUM formula would not need any attention, but you would have to fix up the row formulas. The result would be as follows::
A B C D E 1 Description Count Unit Price Amount Running Total 2 Article-1 3 2.50 =B2*C2 =d2 3 Article-2 2 3.50 =B3*C3 =E2+D3 4 5 Article-3 33 0.50 =B5*C5 =E3+D5 6 Article-4 5 2.95 =B6*C6 =E5+D6 7 Total =SUM(D2:offset(D7,-1,0) Notice that E5 reads =E3+D5 instead of =E4+D5, and the formulas must be filled in manually for D4 and E4.
Insert a Row using a Macro to maintain formulas
I wanted to be able to insert a row and maintain the formulas but not to copy data that would be unique for the line.
If we correct all formulas so that we can insert a row copying formulas and removing constants such as when using the INSRTROW macro described on another page, we would end up with formulas that are a lot easer to to work with -- no changes required. They would appear as follows in a formula view:
A B C D E 1 Description Count Unit Price Amount Running Total 2 Article-1 3 2.50 =B2*C2 =d2 3 Article-2 2 3.50 =B3*C3 =offset(E3,-1,0)+D3 4 =B4*C4 =offset(E4,-1,0)+D4 5 Article-3 33 0.50 =B5*C5 =offset(E5,-1,0)+D5 6 Article-4 5 2.95 =B6*C6 =offset(E5,-1,0)+D6 7 Total =SUM(D2:offset(D7,-1,0) The above formula view generated by the INSRTROW macro would appear as follows in the normal data view, simply fill in the Description, Count and Unit Price for the inserted Row.
A B C D E 1 Description Count Unit Price Amount Running Total 2 Article-1 3 2.50 7.50 7.50 3 Article-2 2 3.50 7.00 14.50 4 0.00 14.50 5 Article-3 33 0.50 16.50 31.00 6 Article-4 5 2.95 14.75 45.75 7 Total 45.75 8 Another way of specifying OFFSET without using OFFSET
see this posting by Ian Brown using a named range for PrevCell of !A1 where A2 is the activecell when defined. i.e. =SUM(H3:PrevCell)
This page is an extension of Insert a Row using a Macro to maintain formulas which describes the INSRTROW macro code.
How to install/use a macro can be found on my formula
Creation of Shortcut keys spreadsheet and an Example from XL95. Same directions as in join.htm#shortcut.
Visit [my Excel home page] [Index page] [Excel Onsite Search] [top of this page]
Please send your comments concerning this web page to: David McRitchie mailto:DMcRitchie@msn.com.