Workaround for dragging down formulas in groups of 3

Hi. I’m having trouble dragging down some formulas in my worksheet, and I’m hoping there is a better workaround. Please see attached example.

On my tab “AUT-NO TABLE” are formulas that refer to data on the “Prep Autype” tab. I need to drag down formulas on the “AUT-NO TABLE” tab in groups of 3 rows: the first 3 rows all refer to cells in row 1 of the “Prep Autype” tab, the next 3 rows refer to cells in row 2 of the “Prep Autype” tab, etc. When I attempt to drag down in groups of 3 rows, the formulas move down in increments of 3, but I need increments of 1.

The only thing I can do now is use Find & Replace to modify the formulas to look at the correct row. I need these formulas to drag down to reference 100s of rows on the “Prep Autype” tab, so Find & Replace each time would obviously take a lot of manual work. Is there anything else I can do here?

I have pasted the first few reference formulas from the spreadsheet below for a quick visual. As you can see, they are in groups of 3 and I need to drag down to reference rows in increments of 1:

[TABLE="width: 1404"]

[tr]

[td]

=UPPER(LEFT('Prep Autype'!$B1,16))

[/td]

[/tr]

[tr]

[td]

=(UPPER(CONCATENATE(LEFT('Prep Autype'!$E1,LEN('Prep Autype'!$E1)-5),": ",LEFT('Prep Autype'!$C1,57))))

[/td]

[/tr]

[tr]

[td]

='Prep Autype'!$L1

[/td]

[/tr]

[tr]

[td]

=UPPER(LEFT('Prep Autype'!$B2,16))

[/td]

[/tr]

[tr]

[td]

=(UPPER(CONCATENATE(LEFT('Prep Autype'!$E2,LEN('Prep Autype'!$E2)-5),": ",LEFT('Prep Autype'!$C2,57))))

[/td]

[/tr]

[tr]

[td]

='Prep Autype'!$L2

[/td]

[/tr]

[tr]

[td]

=UPPER(LEFT('Prep Autype'!$B3,16))

[/td]

[/tr]

[tr]

[td]

=(UPPER(CONCATENATE(LEFT('Prep Autype'!$E3,LEN('Prep Autype'!$E3)-5),": ",LEFT('Prep Autype'!$C3,57))))

[/td]

[/tr]

[tr]

[td]

='Prep Autype'!$L3

[/td]

[/tr]

[/TABLE]

Thanks for your time!!!

Re: Workaround for dragging down formulas in groups of 3

Hi Gadi. Thanks for your response. I also just found that a similar function works as well, using INDEX (sample below). I don't quite understand it, but it works!

=INDEX('Prep Autype'!$O$1:$Q$250,1+INT(ROW('Prep Autype'!Q1)/3.0001),ROW('Prep Autype'!Q1)-INT(ROW('Prep Autype'!Q1)/3.0001)*3)

Thank you!