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