Copying sequential values between worksheets

New Contributor

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

 

LouisaG_0-1639740242914.png

LouisaG_1-1639740361669.png

 

 

4 Replies

@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.

@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

LouisaG_0-1639743618062.png

 

 

 

 

@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.

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