Copying sequential values between worksheets

[LouisaG]

Hi

I would like to copy column total figures (totals in image 1 in yellow) from one worksheet (horizontal) to a column on a different worksheet (image 2). I would like to copy L47, M47, N47 etc. I have a large column to copy and would like Excel to copy sequentially. When I try to do this, it copies as L47, L48, L49. Can anybody help? Thanks Louisa

[#2]

@LouisaG 

Let's say the totals are in L47:T47.

If you have Microsoft 365 or Office 2021:

• Make sure that the cells on the other sheet are empty.
• Select the cell on the other sheet where you want to refer to L47.
• Enter the formula =TRANSPOSE(L47:T47) and press Enter.

If you have an older version:

• Select the range where you want the formulas - 9 cells in a column in this example.
• Enter the formula =TRANSPOSE(L47:T47) and confirm it with Ctrl+Shift+Enter.

[#3]

@Hans Vogelaar Thank you very much for responding. It did not work, so I guess I did not explain what I want to do well enough. I will try to explain once more and then leave you in peace.

Two worksheets

Worksheet 1 has dynamic totals - the figures change each month as more payments are added

Total in column L is L47 containing the formula =SUM(L2:L46)

Worksheet 2 displays updated figures from worksheet 1 ie D5 on W2 displays L47 from W1.

Using your advice, I tested =TRANSPOSE(L47:P47) and have the same formula in all the fields (see top left hand corner of image below). I have added test data to W1 which has not updated W2. 

If you have time to help, I would appreciate it, but if not, thanks so far. Louisa

[#4]

@LouisaG

My apologies, I forgot to include a reference to the other sheet in the formula..

Change the formula to

=TRANSPOSE('W1'!L47:P47)

where W1 is the name of the other sheet.

[#5]

I did wonder about the reference to the other worksheet! Thanks so much, it has worked.
Louisa