Change row number when dragging formula to the right-CodePudding

I hope you are all well.

I have a list of 10 names in column A on Sheet1:

A1 = David
A2 = Jack
A3 = Peter
...

How can I do so I get the names in A1, B1, C1... on Sheet2?

When I type `=Sheet1!A1 in A1 on Sheet2 then I drag the formula to the right I am getting =Sheet1!B1 instead of =Sheet1!A2.`

Thanks

CodePudding user response：

You may try any one of the following ways as per your Excel Version,

Formula used in cell A1 and Fill Across --> Works For All Excel Users

=INDEX(Blad1!$A$1:$A$10,COLUMN(A1))

Formula used in cell A2 no need to fill across since it will spill and applicable to only Excel 2021 & O365 users

=INDEX(Blad1!$A$1:$A$10,SEQUENCE(,10))