Transpose but not transpose

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Transpose but not transpose

Post by LisaGreen »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transpose but not transpose

Post by HansV »

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)
Best wishes,
Hans