Autofill formula that references two columns

  • I would really appreciate help on how to autofill this formula in my spreadsheet! I've been trying to look online for an answer but I can't find what I'm looking for :(


    I THINK this can be accomplished by the INDEX formula but I don't know enough about it.


    The first two "sets" of the formula are below. The formulas reference data from another sheet in my workbook (titled 'TO PRINT') that has data in columns A & B. Each formula "set" contains two lines, which both reference the same row, however each line references a different column (A or B). I want to be able to drag this down so the next formula "set" would reference A4 and B4, followed by A5 and B5, etc. Right now if I try to drag this down it skips rows, it does not autofill consecutively.



    [TABLE="width: 2018"]

    [tr]


    [td]

    =IF('TO PRINT'!A2="","",CONCATENATE("SwapUDP:" & 'TO PRINT'!A2))

    [/td]


    [/tr]


    [tr]


    [td]

    =IF('TO PRINT'!B2="","",CONCATENATE("Get:C:\Users\test\Desktop\COMM TESTING\" & 'TO PRINT'!B2 & ".001"))

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [/tr]


    [tr]


    [td]

    =IF('TO PRINT'!A3="","",CONCATENATE("SwapUDP:" & 'TO PRINT'!A3))

    [/td]


    [/tr]


    [tr]


    [td]

    =IF('TO PRINT'!B3="","",CONCATENATE("Get:C:\Users\test\Desktop\COMM TESTING\" & 'TO PRINT'!B3 & ".001"))

    [/td]


    [/tr]


    [/TABLE]

  • Re: Autofill formula that references two columns


    Yes, the Index function is one way to do it in connection with the Row function. For example, to refer to A2 if your formula is in say A2 of the first sheet, use
    INDEX('To Print'!$A$2:$A$400,ROW()/2)
    and to refer to B2 for a formula in A3 use
    INDEX('To Print'!$B$2:$B$400,(ROW()-1)/2)
    you can the drag the pair down and they will increment correctly (adjust the 400 as needed to get all the rows).

Participate now!

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