Copying Formulas using a Pattern

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:

1. Start with a new, blank worksheet in a workbook containing a worksheet named "Sheet1."
2. In cell A1 enter the formula =Sheet1!B4.
3. Since the formula pattern is every 14 rows, highlight the range A1:A14. Only the first cell should have the formula; the other 13 are blank.
4. Drag the fill handle (the small square at the lower-right corner of the selection downward for a good number of rows--perhaps 1,000 or so. The range A1:A1000 should still be highlighted.
5. Choose Edit | Go To | Special. Excel displays the Go To Special dialog box. (See Figure 1.)
6. Figure 1. The Go To Special dialog box.

7. Click the Blanks option button, then click OK. All the blank (empty) cells in the selection are selected.
8. Choose Edit | Delete | Shift Cells Up/

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.

Related Tips:

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

Leave your own comment:

 *Name: Email: Notify me about new comments ONLY FOR THIS TIP Notify me about new comments ANYWHERE ON THIS SITE Hide my email address *Text: *What is 5+3 (To prevent automated submissions and spam.)

Comments for this tip:

David Handy    21 Jun 2016, 17:27
I need a short cut for the following formula. I am multiplying A1*A71+B1*B71+C1*C71 .... AL THE WAY TO COLUMN BJ
That's a lot of work when I need to do this to 60 columns then to 60 rows.

HELP !!
SO    09 Apr 2016, 23:00
Nice tip! I wish I had seen this about a year ago but it's still handy to know.

I imagine this could be modified to use the COLUMN function instead of ROW.
brandy    20 Jan 2016, 12:14
I have slot numbers that I need to go to run upto number 220 so for example
DX-200-1
DX-200-1A
DX-200-1B
DX-200-1C
DX-200-2A
DX-200-2B
DX-200-2C
so on and so on
Is there a way to make the 200#Change to 201 by just copying??
Akhil    03 Jun 2015, 19:11
None of these formulas work...
Maria    13 Apr 2015, 01:41
Corey Halter    27 Mar 2015, 10:20
How is the referenced cell supposed to look inside the INDIRECT formula in excel 2013? I keep getting reference errors when trying to run the INDIRECT function.
prince    25 Feb 2015, 14:11
Hi ! will you please tell me how can i increase the value of cell address to skip(blank) the few lines.
such as:
801
skip line
skip line
skip line
skip line
skip line
802
skip line
skip line
skip line
skip line
skip line
803

such type of pattern how can we set in excel.
one more thing is
The series of 801
is calling through cell address of sheet1.
in sheet1 all number are in sequential
order. that we want in sheet 2 in given upper pattern.
Thanks !
Peg Molter    30 Aug 2012, 09:55
Great tip. How can we use the same function, but in referring to offset columns, rather than rows? I tried adjusting the formula, but kept getting #REF# errors.

Our Company

Sharon Parq Associates, Inc.

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.