Hi everyone,
I believe Jan Karel has "sort of" addressed this before but not quite this scenario.
What I have is a sheet with a bunch of totals of numbers in a row. These are Sum()s of the columns.
I have a summary sheet with the items going down... Description Col A This total Col B.
I need something that will give the values of the row... but going down.
I would prefer not to have to type "=" and then go to the other sheet, select the cell one to the right and hit return for over 70 columns!
A formula would be perfect.
Any ideas appreciated.
TIA
Lisa
Transpose but not transpose
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transpose but not transpose
Are the items in the same order? If so, you can use the TRANSPOSE function.
Let's say the totals are in A100:BR100 (70 columns) on a sheet named Data Sheet, and that you want them in the summary sheet in B2:B71.
Select B2:B71 on the summary sheet, and enter the following formula:
=TRANSPOSE('Data Sheet'!A100:BR100)
and confirm it with Ctrl+Shift+Enter to save it as an array formula. (It won't work if you merely press Tab or Enter)
Let's say the totals are in A100:BR100 (70 columns) on a sheet named Data Sheet, and that you want them in the summary sheet in B2:B71.
Select B2:B71 on the summary sheet, and enter the following formula:
=TRANSPOSE('Data Sheet'!A100:BR100)
and confirm it with Ctrl+Shift+Enter to save it as an array formula. (It won't work if you merely press Tab or Enter)
Best wishes,
Hans
Hans