Autofil column with data from row?

[skypilotpete]

Is it possible to tell Excel to autofil a column with data from a row?

I would like to be able to take the data from the "totals" row in one sheet (e.g. A42, B42, C42, D42, and automatically put it into a column in another sheet.

Is there any way for an Excel simpleton to do this?

[Mallycat]

highlight the row of data

select the first cell in your destination column

Right click, paste special, select Values and Transpose

[skypilotpete]

That works on a one-off basis, and it is what I am doing at the moment. But if the values in the source row change, this does not automatically update the data in the destination column - which is what I am after.

[skypilotpete]

I have found the answer to my question elsewhere, but I thought someone visiting this forum might like to see the answer.

To automatically fill a column with data from a row, or fill a row with data from a column, use the "Transpose" Function.
E.g:
Select a number of horizontally consecutive cells. i.e F1:J1
enter =Transpose(A1:A5)
then confirm with Ctrl + Shift + Enter
As you change any cell in A1:A5, it updates the corresponding value in F1:J1

If you use the Function dialog box (Insert - Function - Transpose) rather than manually entering "=Transpose(A1:A5)", you still have to finish the process with Ctrl + Shift + Enter to make it work. For reasons which are beyond me, simply selecting "OK" in the dialog box does not work. I wonder if anyone here can explain why???

[sglife]

Here are 2 formulas to help you

=INDEX($B$1:$E$1,0,ROW(A1))
=OFFSET($B$1:$E$1,0,ROW(A1)-1)

[skypilotpete]

Thankyou for taking the time to post this advice. However, I am very ignorant about formulas, and (if you have the time) a bit more information would be helpful - i.e. more details about what precisely they are meant to do and how to use them (e.g, do I select the target cells and put the formula in all of them - or what???). I have played around with the formulas, but can't get them to work in any meaningful way.

Here are 2 formulas to help you

=INDEX($B$1:$E$1,0,ROW(A1))
=OFFSET($B$1:$E$1,0,ROW(A1)-1)

[sglife]

What you need is a sample

[skypilotpete]

Thanks, that's great

[skypilotpete]

Thanks for your help - but one further question. I've got this working for transposing a row to a column, but I can't work out how to adapt it to transpose a column to a row. If you could help with that, it woud be GREATLY appreciated.

What you need is a sample