Autofill not recognizing the intended pattern

Copper Contributor

I am trying to autofill a series of cells from another Sheet but cannot get the autofill to follow the intended pattern correctly.

 

The information in the first cell comes from ='Sheet1'!A1 

I want the autofill in the next cell to also be from 'sheet1' but instead of ='sheet1'!A2, It needs to be ='sheet1'!A11. It skips 10 cells, then the following cell should be the information in A21, A31, A41 and so on.

The autofill function is not recognizing this pattern.  I have tried many ways to get this to work but there seems to be difficulty in the fact that the info in the cell is coming from another Sheet or cell.  How can I get this to work? 

8 Replies

@emil33 

To achieve the desired pattern in autofilling cells from another sheet with skipping rows, you can use a combination of the OFFSET and ROW functions.

Here is how you can do it:

In the cell where you want to start the autofill (let's say it's B1), you can use the following formula:

=INDIRECT("'Sheet1'!A" & (ROW()*10 + 1))

This formula dynamically calculates the row number based on the current row of the cell being filled. It skips 10 rows each time and adds 1 to start from the first row.

Then, you can autofill this formula down to fill the desired range of cells. The formula will adjust automatically for each row, maintaining the pattern of skipping 10 rows.

Make sure to adjust the formula references (e.g., 'Sheet1' and the cell reference) to match your actual sheet name and data range. The text was created with the help of AI

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

@emil33 

Autofill fills cells with data. But you want to fill cells with formulas.


Fortunately, a single formula can do the work:

=INDEX(Sheet1!A$1:A$250, SEQUENCE(25, 1, 1, 10), 1)

(The spaces are optional.)  The SEQUENCE function creates an array of numbers (in this case, they will cause a spill to 25 rows) that refer to the row numbers on Sheet1 that you are interested in.  The INDEX function uses those values to retrieve values from within the specified column A range, and it has the advantage that it is not a volatile function.  (Change the 25 to whatever count is appropriate for your data. Expand the range of column A cells as needed.)

@emil33 

Try this replacing 'vector' with your range:

 

=TAKE(WRAPROWS(vector,10),,1)

@NikolinoDE Thank you for your help but this does not seem to be working.  This is the formula I put in the cell to show the info from another sheet.  =INDIRECT("'Coin Count'!A" & (ROW1*10+1))

This formula works for showing the correct information. 

Any other variation of this formula Excel is not recognizing as a valid formula.  Unfortunately the problem is when I try to autofill the subsequent cells, it is not pulling the correct data.  It is showing the same data in the original cell but the formula for each cell is increasing the ROW number by one. here is what the subsequent cell formula looks like

=INDIRECT("'Coin Count'!A" & (ROW2*10+1))

=INDIRECT("'Coin Count'!A" & (ROW3*10+1))

=INDIRECT("'Coin Count'!A" & (ROW4*10+1))

=INDIRECT("'Coin Count'!A" & (ROW5*10+1))

The most confusing part is it's not even showing the data from those cells, only a copy of the data in the original cell.  

Thank you for your help. This didn't quite work but maybe close. Can you explain the part of the formula where the numbers (25, 1,1,10),1).
I think I understand the 25 and 10 but the 1s are throwing me off. Maybe if I understand the whole formula I can tweak it to get it to work.
As for now the cells are all only showing me, #NAME?
Thank you for your help but where I put the Sheet, Row and Colum desired. for which I would like to show the data from?

@emil33 

The documentation for the SEQUENCE function for some reason "hides" its description; on that page, you should click the down arrow to the right of "Syntax".

 

The first argument (25) is the number of rows to generate numbers for.  I just guessed that you could use 25; feel free to change that number.  The second argument (1) is the number of columns to generate numbers for.  The third argument (also 1) is the starting number to generate, and the fourth argument (10) is how much to increment each generated number.

 

But the #NAME error suggests that your version of Excel does not support the SEQUENCE function, which is available in Excel 2021 and later versions.  Which version of Excel are you using?

 

@emil33 Try entering the following formula in cell A1 (on the destination sheet), then drag down as needed:

 

=INDEX(Sheet1!A:A, ROW() * 10 - 9)

 

However, if the first cell where you want to start the series is NOT in row 1 of the destination sheet, you will need to modify the formula to account for the row number of the first cell. For example, if the first cell is C5, enter the following formula in cell C5, then drag down as needed:

 

=INDEX(Sheet1!A:A, (ROW() - ROW($C$5) + 1) * 10 - 9)

 

See attached sample workbook, if needed...