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

## Files

• Re: Workaround for dragging down formulas in groups of 3

Hi Lizzie, I transformed your 3 formulas to this general structure:

=UPPER(LEFT(INDIRECT("'Prep Autype'!B"&INT((ROW()+1)/3)),16))

=(UPPER(CONCATENATE(LEFT(INDIRECT("'Prep Autype'!E"&INT((ROW()+1)/3)),LEN(INDIRECT("'Prep Autype'!E"&INT((ROW()+1)/3)))-5),": ",LEFT(INDIRECT("'Prep Autype'!C"&INT((ROW()+1)/3)),57))))

=INDIRECT("'Prep Autype'!L"&INT((ROW()+1)/3))

As you can see the new formulas are independent of any fixed row numbers in the cell reference for sheet Prep Autype. In the attachment of this reply, I included the steps to the formula within Sheet1.

I added the above formulas to cells A20:A22 in sheet AUT - NO TABLE so you can simply pull it down and see that it works as you requested.

forum.ozgrid.com/index.php?attachment/45803/

## Files

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

• Re: Workaround for dragging down formulas in groups of 3

That's one hell of a formula you've got there I think the one I offered is by far simpler. Glad to hear you got it sorted out though.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!