I want to drag down a column but keep the same row of data by going into the next column on another sheet.
For example I have sheet1 with random data in row cells: A1 B1 C1 D1.
On sheet 2 I want to copy this row of data into a column for example: C3 C4 C5 C6.
I have tried used =OFFSET('sheet1'!$A$1,0,1) but this just keeps giving the data on cell B1.
How do I drag down the cells (autofill), but change the column letter and keep the same row number from another sheet?