The information in this article applies to:
SUMMARYWhen you fill formulas down a column or across a row, the relative references are, by default, incremented by one. The formulas can be altered so that they are incremented by a user-defined amount through the use of the OFFSET function. MORE INFORMATIONThe formula for incrementing by more than a row is:
where <Starting_Cell> is the absolute reference to the first cell in the
range of interest, <Current_Row> is the row of the cell in which the
formula is being entered, and <Inc> is the number of rows to increment.
The formula for incrementing by more than column is:
where <Starting_Cell> is the absolute reference to the first cell in the
range of interest, <Current_Column> is the number associated with the
column of the cell in which the formula is being entered, and <Inc> is the
number of columns to increment.
Example Incrementing a Formula by More Than One Row
Example Incrementing a Formula by More Than One Column
Example Using ISEVEN FunctionThis formula is not restricted to extracting data. It can also be used within other formulas. By using the following formula, you can test if the numerical data in every fourth row is even:
where <Starting_Cell> is the absolute reference to the first cell in the
range of interest, <Current_Row> is the row of the cell in which the
formula is being entered, and <Inc> is the number of rows to increment.
NOTE: This formula requires that the Analysis ToolPack is included in the Add-Ins. To do this, on the Tools menu, click Add-Ins and click to select the check in the box next to Analysis ToolPack.
Q151337 Incrementing Relative References by More Than One Cell in VBA Additional query words: 5.00c 7.00a 5.00a 97 8.00 98 XL98 XL97 XL7 XL5 howto database mailing labels mail nth multiple multiples
Keywords : kbualink97 kbdta xlformula |
Last Reviewed: October 25, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |