Drag down formula and change ROW references instead of COLUMNS

Question

In excel I have sheet1 contains total every month. (please see image below)

Then in my sheet2 it will display horizontally. (please see image below)

I used the formula =Sheet1!$B$1, my problem is when i drag it to the right it increments the column letters. How should i make it increment the row only and the column letter is constant in column "B".

Any ideas and alternatives is much appreciated.

Thank you in advance!

Strobotron

the title makes no sense. If you drag a formula up/down it will change row references. It is when you drag a formula across,then it will change columns.. It looks like you want to drag your formula to the right and change rows – Homicidal

Answer 1

You can use OFFSET:

=OFFSET(Sheet1!$B$1,COLUMN()-2,0)

Or TRANSPOSE: Select B2:M2 and then enter

=TRANSPOSE(Sheet1!$B$1:B12)

as an array formula (using Ctrl+Shift+Enter)

Electrocorticogram