How to increase cell row number by 1 when drag down to autofill

[marumaru]

Hello all,

This is how I want to display data from the ‘Info’ sheet. But when I use autofill by dragging down, it skips rows and goes from row 1 to row 4. How can I make it go to row 2 instead? Any advice is welcome. Thanks!

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

My info sheet:

marumaru.xlsm
	A	B	C	D	E	F	G
1	31/05/2023	338858725	1	8	UPS Ground 1	ZZ102BL	z12123123
2	31/05/2023	338953941	2	6	UPS Ground 2	ZZ102BK	z1456545645
3	31/05/2023	338967317	3	4	UPS Ground 3	ZG599RG	z897897897
4	31/05/2023	338974676	4	2	UPS 2nd Day Air	GG218BL	z4564874869
info

In tracking, put these formulas in row 1 and then drag down and see how that goes.

marumaru.xlsm
	A	B	C	D	E
1		338858725		31/05/2023
2		1		ZZ102BL	8
3	UPS Ground 1		z12123123
4		338953941		31/05/2023
5		2		ZZ102BK	6
6	UPS Ground 2		z1456545645
7		338967317		31/05/2023
8		3		ZG599RG	4
9	UPS Ground 3		z897897897
10		338974676		31/05/2023
11		4		GG218BL	2
12	UPS 2nd Day Air		z4564874869
tracking

Cell Formulas
Range	Formula
A1:A12,C1:C12	A1	=CHOOSE(MOD(ROWS(A$1:A1),3)+1,INDEX(info!E:E,INT((ROWS(A$1:A1)-1)/3)+1),"","")
B1:B12	B1	=CHOOSE(MOD(ROWS(B$1:B1),3)+1,"",INDEX(info!B:B,INT((ROWS(B$1:B1)-1)/3)+1),INDEX(info!C:C,INT((ROWS(B$1:B1)-1)/3)+1))
D1:D12	D1	=CHOOSE(MOD(ROWS(D$1:D1),3)+1,"",INDEX(info!A:A,INT((ROWS(D$1:D1)-1)/3)+1),INDEX(info!F:F,INT((ROWS(D$1:D1)-1)/3)+1))
E1:E12	E1	=CHOOSE(MOD(ROWS(E$1:E1),3)+1,"","",INDEX(info!D:D,INT((ROWS(E$1:E1)-1)/3)+1))

 

[marumaru]

Thank you it works perfectly!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.