Syntax. OFFSET(reference,rows,columns,height,width)

Definition. This function returns a cell reference that is a specified number of rows and columns from a given reference.

Arguments

Background. This function doesn’t move cells on the worksheet; it moves the reference to a specified range. If you specify a value for the rows and columns arguments beyond the current sheet, the OFFSET() function returns the #REF! error.

The function expects integers for the four last arguments, and the last two integers must be positive. If the expressions in these arguments are evaluated to fractions, the decimal places are removed. No error occurs.

If you don’t specify the height and width arguments, Excel assumes that the new reference has the same height and width as the initial reference.

If the height argument is smaller than the height of the destination range, the remaining cells display the #N/A error. The same applies to the width argument. If the value is 1, the corresponding rows and columns are repeated in the remaining cells. Figure 10-8 shows an example.

Figure 10-8. The height and width arguments.

The reference named MyRange has the dimensions two rows x three columns. The formula

{=OFFSET(MyRange,0,1,2,1)}

moves the target to the first cell in the range (B13) and offsets this by zero rows and one column, taking the starting point to cell C13. The height and width parameters extend the range to two rows and one column to target C13:C14. The destination range F13:I15 has the dimensions three rows x four columns. The destination range is filled with the values from C13:C14, repeating this four times across the destination range, but the remaining row (15) is filled with the error #N/A.

Examples. The following examples illustrate how the OFFSET() function is used.

Addressing Single Cells. Use this function to address single cells in the original named range. In this case, you don’t move the entire range but only the upper-left corner of the range. If you specify the value 1 for the height and width, the result is a single cell.

Assume that your range, MyRange, includes cells B5 through D6. Cell D6 is the last cell in the range and can be addressed with the array formula

{=OFFSET(MyRange,1,2)}

To move the required reference to the right or down, start at the upper-left cell of the range: two cells to the right and one cell down.

This method is especially useful if you use dynamic ranges, which change over time (for example, a dynamic list, a manually entered list, or a list updated with a database). In this case, use the COUNT() or COUNTIF() function to specify the position. This is explained in the following examples.

Variable-Length Lists. Assume that you have a list like the one shown in Figure 10-9. You want to filter the information in the list using database functions. The list constantly changes because records are added or removed.

Figure 10-9. Evaluating lists with dynamic names.

Create the data list in a worksheet named Calculations. The titles of the columns don’t have to match the list titles (for example, you could use Category instead of Categories).

Select the list titles. Select the Insert/Names/Define menu option (in Excel 2003 and earlier) or click Define Name in the Defined Names group on the Formula tab (Excel 2007 and Excel 2010). Enter the name list for the range defined by the following formula:

=OFFSET(Calculations!$B$6:$E$6,0,0,COUNT(Calculations!$B:$B)+1)

Based on the number of numeric entries in column B, the upper-left cell of the title range ($B$6:$E$6) is dynamically extended by the adjustable height argument. Remember that +1 is necessary to include the titles in the list.

You can now add up the invoice amounts in the English category using the following formula:

=DSUM(list,E6,D1:D2)

The DSUM() function takes the values identified by the list range and sums the values in the Amount column according to the criteria set in the range D1:D2 (Category, English). Other entries are evaluated with DCOUNT()

=DCOUNT(list,B6,D1:D2)

to return a count of the transaction. (The second argument can be empty.)

If you use Excel 2007 or Excel 2010 and format the list as a table, you don’t have to specify the range name. If the table has the name Table1 (the default name), the formula is

=DCOUNT(Table1[#All],B6,D1:D2)

Dynamic Charts. You can use the method explained in the previous example to generate dynamic charts. Define a dynamic range name and use the named range to create the chart. Use a named range for the legend and data, as in these formulas:

=OFFSET(Charts!$C$4,0,Charts!$B$23)

=OFFSET(Charts!$C$5:$C$19,0,Charts!$B$23)

where the value in cell B23 defines which column should be selected to generate the chart.

Another Address Example. The third example for the ADDRESS() function returns the content of a cell with the ADDRESS() and INDIRECT() functions. You can also return the information by combining the first two examples in this section.

To use the sum from Figure 10-1, shown earlier, in a different cell on the Payments worksheet, give the dynamic range the name Payments:

=OFFSET(Payment!$A$1:$D$1,0,0,COUNT(Payment!$A:$A)+1)

The range (including its titles) is adjusted according to column A. You can obtain the reference to the lower-right cell of the range that contains the current sum with the following array formula:

{=OFFSET(Payment,ROWS(Payment)-1,3)}

Remember that the row and column numbers start with 0. If you have Excel 2007 or Excel 2010, you can use a table instead of a dynamic name:

{=OFFSET(Table2[#All],ROWS(Table2[#All])-1,3)}