How to setup transferring data from row to column?

[Toto Sanderson]

Can anyone help me here:

How do I setup transfering data in a row to a column on another sheet so
that when I input anything in the row, the column in the other sheet will be
automatically filled.

I tried =Sheet1!A1 and then copy down. But the second cell down becomes
=Sheet1!A2, whereas I want it to be =Sheet1!B1

Thanks in advance!

 

[Roger Govier]

Hi

One way, enter in Sheet2 cell A1
=OFFSET(Sheet1!$A$1,column()-1,ROW()-1)
and copy down (and across if required)

 

[Toto Sanderson]

used your formula and copy down... But it doesn't work. The cell down has
exactly the same formula

 

[Dominic]

Toto,

Did you try the formula with data?

It worked for me. The "formula " won't change because it uses relative
references for the particular row/column it is in. It will look the same, but
should achieve your desired results as tested on my machine.

HTH

 

[Toto Sanderson]

Thank you. I looked up the definition of OFFSET and finally worked out why it
didn't work.

The actually Cell range I am referring to is C15:AG15. And so I used:
=Offset(Sheet1!$B$14,Column(B14)-1,Row(B14)-13)
Then I copied down, it worked!

I appreciate all your help!

 

[Roger Govier]

Hi Toto

Glad you looked it up and worked it out for yourself. Thank you for
letting us know.
It would, incidentally, also have worked with my proposal, if you had
just changed cell $A$1 to $C$15.
In your first post, you said you were trying to copy from cell A1.