Search code examples
excelexcel-formulatranspose

How to transpose every nth cell horizontally onto another Sheet vertically

This combination of transposing and referencing I can't seem to figure out. On Sheet2 I have the following Data which is the original reference source. The data is every 8th cell in my example

D4:22  L4:16  T4:10

I'd then like to have on Sheet1 which is referenced to the respective cells on Sheet2

F6:22  F7:16 F8:10

There is data between the numbers on Sheet2 so I am unable to just collect all the data in a row and minus the empty cells which I know how to do. I'd like to drag down F6 which is the part I realize I've created my formula wrong because it doesn't drag down. The data on Sheet2 goes on for a hundreds of cells which is why I can't just create a simple =Sheet2!D4

Solution

  • You can use INDEX to get the cells you want

    Something like (For cell Sheet1!F6)

    =INDEX(Sheet2!$4:$4,1,ROW()*8-44)

    And copy down