Forum Discussion

Copper Contributor

Dec 17, 2021

Copying sequential values between worksheets

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

MVP

Dec 17, 2021

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.

LouisaG
Copper Contributor
Dec 17, 2021
HansVogelaar 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
- HansVogelaar
  MVP
  Dec 17, 2021
  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.
  - LouisaG
    Copper Contributor
    Dec 17, 2021
    I did wonder about the reference to the other worksheet! Thanks so much, it has worked.
    Louisa

Resources