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"]

=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!!!