How to drag down columns but change column letter?

Question

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?

Answer 1

Try,

=INDEX(Sheet1!$1:$1, 1, ROW(1:1))

Drag down as required. Here it is on a single worksheet.

    

Your formula would have worked as,

=OFFSET('sheet1'!$A$1, 0, ROW(1:1)-1)

But the OFFSET function is volatile¹ and best avoided if possible.

---

¹ _{Volatile functions recalculate whenever anything in the entire workbook changes, not just when something that affects their outcome changes. Examples of volatile functions are INDIRECT, OFFSET, TODAY, NOW, RAND and RANDBETWEEN. Some sub-functions of the CELL and INFO worksheet functions will make them volatile as well.}