# Copying Formulas using a Pattern

by Allen Wyatt
(last updated February 24, 2021)

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.

##### Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

##### MORE FROM ALLEN

Adding an Ellipsis to the Beginning of Some Paragraphs

The Find and Replace feature of Word is very powerful. You can even use it to add a unique character to the beginning of ...

Discover More

Typing Check Marks into Excel

Need to enter a check mark into a cell? There are a number of ways you can get the desired character, depending on the ...

Discover More

Using a Week Number as One Criterion in a Formula

The SUMIFS function can be quite powerful in conditionally summing information based on criteria you specify. This tip ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

Can't Copy Data between Workbooks

Edit a group of workbooks at the same time and you probably will find yourself trying to copy information from one of ...

Discover More

Editing the Same Cell in Multiple Sheets

When creating a workbook, you may need to make changes on one worksheet and have those edits appear on the same cells in ...

Discover More

Getting Rid of Non-Printing Characters Intelligently

Is your worksheet, imported from an external source, plagued by non-printing characters that show up like small boxes ...

Discover More
##### Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. Youâ€™ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is four less than 6?

2020-07-13 10:51:15

Brian

Weluzudi, this was perfect for my needs! When I had a duplicate, I manually adjusted the formula by changing the '+1' to '+2' or '+3' as needed (only had 150 occurrences. And I was able to adjust to read from a separate sheet: =IFERROR(INDIRECT("'Sheet2'!J"&((ROW()-1)*1/2)+1),"")

2019-11-04 00:05:32

Wolf

OMG Weluzudi,

I was looking for this solution left right and center and the only way other people managed to do it was by macro.

The best tricks are always in the comments...

2018-12-19 20:44:54

Weluzudi

I needed to do almost the opposite. I needed the formula to increment by 1 every 22 rows, leaving the 21 rows between blank. I used a modification of the formula above and it worked great. Here is what I used:

=IFERROR(INDIRECT("J"&((ROW()-1)*1/22)+1),"")

The information was in column "J".

The "IFERROR" portion handles the error received when the resulting row calculation is not an integer and puts a blank in that cell.

Hope someone finds this useful. I have been looking for this solution for a while, but today I really needed it.

Thanks.

2018-05-16 11:38:23

Willy Vanhaelen

Probably you can only do it by using a macro. Here is a small one that will do the job:

Sub SheetPtrn()
Dim R As Integer, X As Integer, Y As Integer
R = 99 'number of rows to fill
For X = 1 To R Step 3
Y = Y + 1
Range(Cells(X, 1), Cells(X + 2, 1)) = "Sheet1!A" & Y
Next X
End Sub

2018-05-15 19:42:16

r

How do you reference a sheet in a pattern of threes? Like this:

Sheet1!A1
Sheet1!A1
Sheet1!A1
Sheet1!A2
Sheet1!A2
Sheet1!A2
Sheet1!A3
Sheet1!A3
Sheet1!A3

I have thousands of rows I need to autofill for & cannot do this by hand.

2018-01-27 22:16:16

Ben

This solved the exact problem I was having. I spent a good 10 min trying to understand the formula once I put it in excel because I didn't see the explanation below hahaha. You are quite simply a genius. To thank you I clicked on every ad on your site a good 20 times a piece.

2017-07-06 06:09:01

BOB

How can I use this for every nth COLUMN instead of ROW?

2017-02-16 08:21:32

Shaun

Essentially, I am trying to retrieve data from every 9th row (11, 20, 29 ) in a range of cells in 1 column, which I think is very similar to the example you've given, I just can't seem to make it work.

2017-02-16 08:14:09

Shaun

Hi there,

Thanks for this post, just had 1 question - in the formula below what is the function of the (+4)?

=INDIRECT("Sheet1!B"&((ROW()-1)*14)+4)

Cheers!

2017-01-04 10:57:07

Daniel

##### This Site

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.