Written by Allen Wyatt (last updated February 24, 2021)
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.
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!
Using the mouse to select a large cell range can be frustratingly slow. If you want to make copying to a large range of ...
Discover MoreHave you ever copied information from one worksheet to another, only to have the information you paste not look the way ...
Discover MoreWant an easy way to insert a new row in a worksheet and copy everything from the row above? (You end up with two ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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,
you just made my day!
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
Really helpful!
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 © 2024 Sharon Parq Associates, Inc.
Comments