Forum Discussion
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 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
4 Replies
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.
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
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.
