Excel, drag 3 different commands but only increase them by one not 3?

Question

I want to get a lot of data from Sheet1 to Sheet2 but when I pull a command to the right it should only increase the "C" to a "D" every third row. Because I have 3 commands to pull to the right and all should just increade by one not by 3.

A1 looks like this:

=WENN(Sheet1!C3>0;Sheet1!C$2;"")

B1 like this:

=WENN(Sheet1!C3>0;Sheet1!$A3;"")

C1 like this:

=WENN(Sheet1!C3>0;Sheet1!C3;"")

When I pull it to the right it they change like this:

=WENN(Sheet1!F3>0;Sheet1!F$2;"")
=WENN(Sheet1!F3>0;Sheet1!$A3;"")
=WENN(Sheet1!F3>0;Sheet1!F3;"")

But I want:

=WENN(Sheet1!D3>0;Sheet1!D$2;"")
=WENN(Sheet1!D3>0;Sheet1!$A3;"")
=WENN(Sheet1!D3>0;Sheet1!D3;"")

I hope you know what I mean. Is there any way to do this?

This is a hard question to answer without knowing what exactly you want to do. I think the best solution for you would be to rethink your strategy, and order your data differently. Perhaps move B1 and C1 to A2 and A3. — Luuklag, Apr 05 '18 at 09:19

score 0 · Answer 1 · answered Apr 05 '18 at 10:20

It is possible with VBA:

What you entered in Excel in VBA language is the following:

Range("A1").Formula = "=IF(Sheet1!C3>0,Sheet1!C$2,"""")"
Range("B1").Formula = "=IF(Sheet1!C3>0,Sheet1!$A3,"""")"
Range("C1").Formula = "=IF(Sheet1!C3>0,Sheet1!C3,"""")"

If now you put a small loop you can add 1 to the Column "C" each time you reenter your formula. I used a small Function for this (from Function to convert column number to letter?)

Sub Macro1()

Dim k As Integer
k = 1
    Range("A1").Formula = "=IF(Sheet1!C3>0,Sheet1!C$2,"""")"
    Range("B1").Formula = "=IF(Sheet1!C3>0,Sheet1!$A3,"""")"
    Range("C1").Formula = "=IF(Sheet1!C3>0,Sheet1!C3,"""")"

    For k = 1 To 10 ' Here change 10 into whatever you need.

        Cells(1, 3 * k + 1).Formula = "=IF(Sheet1!" & Col_Letter(3 + k) & "3>0,Sheet1!" & Col_Letter(3 + k) & "$2,"""")"
        Cells(1, 3 * k + 2).Formula = "=IF(Sheet1!" & Col_Letter(3 + k) & "3>0,Sheet1!$A3,"""")"
        Cells(1, 3 * k + 3).Formula = "=IF(Sheet1!" & Col_Letter(3 + k) & "3>0,Sheet1!" & Col_Letter(3 + k) & "3,"""")"
    Next k
End Sub

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

Hope it helps

score 0 · Answer 2 · answered Apr 05 '18 at 10:25

If you want a formula based solution

A1:

=IF(INDIRECT(CONCATENATE("Sheet1!";LEFT(SUBSTITUTE(ADDRESS(1;(COLUMN(A:A)+2)/3+2);"$";"");1);"3"))>0;INDIRECT(CONCATENATE("Sheet1!";LEFT(SUBSTITUTE(ADDRESS(1;(COLUMN(A:A)+2)/3+2);"$";"");1);"$2"));"")

B1:

=IF(INDIRECT(CONCATENATE("Sheet1!";LEFT(SUBSTITUTE(ADDRESS(1;(COLUMN(A:A)+2)/3+1);"$";"");1);"3"))>0;Sheet1!$A3;"")

C1:

=IF(INDIRECT(CONCATENATE("Sheet1!";LEFT(SUBSTITUTE(ADDRESS(1;COLUMN(C:C)/3+2);"$";"");1);"1"))>0;INDIRECT(CONCATENATE("Sheet1!";LEFT(SUBSTITUTE(ADDRESS(1;COLUMN(C:C)/3+2);"$";"");1);"1"));"")

I can't guarantee that there's no mistake as I don't have your workbook, however when I made a test of the formula it worked fine for me.

Excel, drag 3 different commands but only increase them by one not 3?

2 Answers2