Incrementing a row by more than when dragging down

  • Hi I'm hoping that someone can help me with a solution that I've been trying to resolve for days. I'm designing a greyhound results spreadsheet to show the winning traps in each race. The results are in sets of 6 dogs per race and I'm only looking to record results where there are winners (no dead heats). In need to know how do I increment row I3 to N3 and subsequent rows when dragging down. I need it check the next set of race results and so forth. IAs I mentioned I'm only going to base it on races that involve 6 dogs so I need to be able to drag the formula down so that it will always move to the next set of 6 dogs in the proceeding race. Basically I need cell I3 to be similar to =VLOOKUP($I$1,D8:E13,$E$1,0) when I drag down and so forth for cells J3, K3, L3, M3 & N3 and proceeding rows below this.



    Many thanks in anticipation.

  • Hello and Welcome to the Forum :)


    Attaching a sample file would make everything a lot easier ...;)


    By design, images are inert objects ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello Alex,


    Thanks for your file


    Formula in cell I2 can be copied all the way in your whole Range I2:N19


    see attached


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • The first thing is too remove the MergedCells, they are unnecessary and will cause problems.


    Next consider formatting the data as a Table. A Table will automatically add formulas and formatting as rows are added.


    Overview of Excel tables

  • Wow Carim. You're a life superstar.

    Many, many thanks for this. :thumbup:


    Glad this could help :)


    Thanks a lot for your Thanks :thumbup:... AND for the Like :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • The first thing is too remove the MergedCells, they are unnecessary and will cause problems.


    Next consider formatting the data as a Table. A Table will automatically add formulas and formatting as rows are added.


    Overview of Excel tables


    The first thing is too remove the MergedCells, they are unnecessary and will cause problems.


    Next consider formatting the data as a Table. A Table will automatically add formulas and formatting as rows are added.


    Overview of Excel tables

    Many thanks Roy.

    I'll definitely take this on board moving forward.:thumbup:

  • Hello again,


    Forgot to mention that given the structure you have picked ...


    There is a double ' Transpose ' ( by column and by row ) which are entirely dependent on the first row and the first column of your Output Range..... (in your case cell I2)


    So, should you decide to change things around and modify your worksheet, you would need to adjust the formula accordingly ...


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)