Copying cell ref from rows to columns

[OSUBill]

This may be a simple question but I have tried everything I know.

I want to drag a cell reference across a row. The problem is I want to reference cells from another work sheet that is formatted in Columns.

PROBLEM:
I have 12 months across a row. A1:L1
I want to pull in data to these fields from another worksheet but the months are listed in Columns A1:A12
I need to be able to click on A1 and drag formula to L1.

EXAMPLE:
A1 cell ref whould be ('Sheet2'!A1),
B1 cell ref should be ('Sheet2'!A2),
C1 cell ref should be ('Sheet2'!A3).
etc.
I tried putting in the following ('Sheet2'!$A1) but it doesn't drag the cell ref properly.

Thanks for any help.

 

[onlyadrafter]

Hello and welcome to the board,

You need to use the OFFSET formula.

e.g.

=OFFSET(Sheet6!$A$1,0,ROW(Sheet7!A1)-1)

You will have change Sheet names/cell refs to suit.