Forum Discussion
Drag fill not continuing or recognising pattern for formulae.
So I am attempting to use drag fill to speed up my data entry input.
I am using the OFFSET function where I have the following:
=OFFSET($A$1,19,0)
=OFFSET($A$1,38,0)
=OFFSET($A$1,57,0)
In my opinion the pattern is clear so don't see how this isn't recognised.
I've even tried switched the multiples of 19 to clearer sums such as:
=OFFSET($A$1,19*1,0)
=OFFSET($A$1,19*2,0)
=OFFSET($A$1,19*3,0)
But a pattern is clearly still not recognised.
But rather than continuing the pattern and giving me the desired
=OFFSET($A$1,76,0)
=OFFSET($A$1,95,0)
=OFFSET($A$1,114,0)
It is just cycling the previous results to give me
=OFFSET($A$1,19,0)
=OFFSET($A$1,38,0)
=OFFSET($A$1,57,0)
=OFFSET($A$1,19,0)
=OFFSET($A$1,38,0)
=OFFSET($A$1,57,0)
The cycle of depends on how many I select, so if I were to manually input 4 and then drag fill it would cycle the 4 same results.
I am unsure how to fix this issue so any help would be appreciated.
Thanks in advance.
You are correct that Excel fails to recognize such patterns. So you have to specify the offset explicitly.
Let's say your first formula is in D2.
Change the formula to
=OFFSET($A$1,19*(ROW(D2)-ROW($D$2)+1),0)
and fill down. Alternatively, use
=INDEX($A:$A,19*(ROW(D2)-ROW($D$2)+1)+1)
3 Replies
You are correct that Excel fails to recognize such patterns. So you have to specify the offset explicitly.
Let's say your first formula is in D2.
Change the formula to
=OFFSET($A$1,19*(ROW(D2)-ROW($D$2)+1),0)
and fill down. Alternatively, use
=INDEX($A:$A,19*(ROW(D2)-ROW($D$2)+1)+1)
- cameronmarshallCopper ContributorWorked perfectly. Thank you.
- OliverScheurichGold Contributor