Forum Discussion
Autofill not recognizing the intended pattern
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.)
- 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?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?
