Forum Discussion
Solved
Fill a column sequentially with individual cells repeated
Good day,
I'm trying to fill a column that references a different sheet like this:
='Historic'!F175
='Historic'!G175
='Historic'!F176
='Historic'!G176
='Historic'!F177
='Historic'!G177
='Historic'!F178
='Historic'!G178
And so on, but if I write the first four lines and drag down, it autocompletes like this:
='Historic'!F175
='Historic'!G175
='Historic'!F176
='Historic'!G176
='Historic'!F179
='Historic'!G179
='Historic'!F180
='Historic'!G180
And of course, if I keep dragging the problem keeps happening. How can I fix it?
Thank you everyone in advance!
Let's say the first formula is in B2. Change it to
=INDEX(Historic!$F$175:$G$1000000, QUOTIENT(ROW(B2)-ROW($B$2), 2)+1, MOD(ROW(B2)-ROW($B$2), 2)+1)
If it is in another cell. adjust B2 and $B$2 to that cell.
Fill down.
Let's say the first formula is in B2. Change it to
=INDEX(Historic!$F$175:$G$1000000, QUOTIENT(ROW(B2)-ROW($B$2), 2)+1, MOD(ROW(B2)-ROW($B$2), 2)+1)
If it is in another cell. adjust B2 and $B$2 to that cell.
Fill down.
- Thank you so much!
- if you have excel 365 maybe just use =TOCOL('Historic'!F175:G299)
