Using OFFSET to maintain formulas

Location:   http://www.mvps.org/dmcritchie/excel/offset.htm
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

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          

DoubleClick Event Macro to insert a SUM total using OFFSET (#dclick)

The following is an Event macro and have used ActiveCell instead of target so you can copy code and paste into whatever macro you are using it in. 
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
      As Range, Cancel As Boolean)
 Cancel = True    '-- Get out of Edit mode
 ActiveCell.Formula = "=SUM(" & Cells(5, _
   ActiveCell.Column).Address(1, 0) _
   & ":OFFSET(" & ActiveCell.Address(0, 0) & ",-1,0))"
End Sub

Another way of specifying OFFSET without using OFFSET   (#namedrange)

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)

Use of Index in a RANGE   (#range)

INDEX(array, row_num, column_num)                                   a single area range is an array
INDEX(reference, row_num, column_num, area_num)            reference is one or more cell ranges

=SUM(D2:D14) — use of a RANGE of course you would use OFFSET as shown above.
=SUM("Motors") — specification of a range in a worksheet function

To get to the third column in the first row of the range "Motors"
=INDEX("Motors", 1,3)

The equivalent with OFFSET would be
=OFFSET("Motors",0,2)

Use of
=INDEX((D1:F5,E3:G7),1,3,2) returns the value of cell G3
=INDEX((motors,E3:g7),1,3,1) if Motors is D1:F5, returns the value of cell F1

Also see

Use of INDEX in totals and subtotals   (#index)

The use of OFFSET allows you to refer to the previous row without actually using the previous row number in a cell address.
A300:  =SUM(A2:OFFSET(A300,-1,0)

The use of INDEX allows you to always add up to the row above without regard to typing in any specific cell address.
A300:  =SUM(A2:INDEX(A:A,ROW()-1))

The use of SUBTOTAL allows you to ignore totals in other SUBTOTALS and, if filtered, it only includes filtered values (don't confuse filter with hidden)
A300:  =SUBTOTAL(9,A2:OFFSET(A300,-1,0)
A300:  =SUBTOTAL(9,A2:INDEX(A:A,ROW()-1))

The starting point A2 in column A, allows for headers on Row 1.

The use of INDEX in this manner was found in a posting by Gord Dibben (2005-05-03, worksheet.functions) appears to be orginally posted in English newsgroups by Hans Knudsen 1997-07-27

This page is an extension of  Insert a Row using a Macro to maintain formulas  which describes the INSRTROW macro code.

INDIRECT Worksheet Function

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.

You are one of many distinguished visitors who have visited my site here or in a previous location since March 24, 1999 

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 send email comments

Copyright © 1997 - 2005,  F. David McRitchie,  All Rights Reserved