Written by Allen Wyatt (last updated August 10, 2024)
This tip applies to Excel 97, 2000, 2002, and 2003
Suppose you have a worksheet that contains a series of formulas in cells A1:A3. Cell A1 contains the formula =Sheet1!B4, cell A2 contains =Sheet1!B18, and cell A3 contains =Sheet1!B32. You might need to continue this pattern down the column, such that A4 contains =Sheet1!B46, etc.
The problem is, if you simply copy the cells, the pattern isn't continued. Instead, the formulas are adjusted based on the target cell's relation to the source cell. Thus, if you paste A1:A3 into A4:A6, then A4 will contain =Sheet1!B7, which is not what you want. (This happens whether you specifically copy and paste or fill the cells by dragging the fill handle.)
There is no way to continue a pattern while copying a formula. Instead, you need to revisit how you put the formula together in the first place. For instance, consider this formula:
=INDIRECT("Sheet1!B"&((ROW()-1)*14)+4)
This formula constructs a reference based on the position of the cell in which the formula is placed. If this formula is placed in cell A1, then the ROW function returns 1, the row in which the formula is placed. Thus, the formula becomes this:
=INDIRECT("Sheet1!B"&((1-1)*14)+4) =INDIRECT("Sheet1!B"&(0*14)+4) =INDIRECT("Sheet1!B"&0+4) =INDIRECT("Sheet1!B"&4) =INDIRECT("Sheet1!B4")
What is returned is the value at Sheet1!B4, just as originally wanted. When you copy this formula down the column, however, the ROW function returns something different in each row. In effect, the ROW function becomes a way to increase the offset of each formula by 14 rows from the one before it--just what you wanted.
You can also use a slightly different approach, this time using the OFFSET function:
=OFFSET(Sheet1!$B$4,(ROW()-1)*14,0)
This formula grabs a value based on the row in which the formula is placed (again, using the ROW function) and offset from cell Sheet1!B4. Placed into the first row of a column and then copied down that column, the formula returns values according to the pattern desired.
Another approach is to create the desired formulas directly. You can best do this by following these steps:
Figure 1. The Go To Special dialog box.
The result is that you end up with only the formulas, with the pattern desired.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3067) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
Copy a formula from one cell to another on the same worksheet, and Excel adjusts any relative references in the formula ...
Discover MoreWhen you are copying a cell from one place to another (perhaps even to a different worksheet), you may not want to copy ...
Discover MoreWhen creating a workbook, you may need to make changes on one worksheet and have those edits appear on the same cells in ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments