Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Excel 2007 - Transpose rows of data to columns with a formula to link the data for changes

I would like to transpose several rows of data into columns of data so that I can use a pivo table on it.  I would like to use a formula so if I change something in the row areas, the transposed area also changes.  I have attached  a small file with some data in the rows.

Thanks, Eric
Transpose.xlsx
0
ekaplan323
Asked:
ekaplan323
  • 2
  • 2
1 Solution
 
barry houdiniCommented:
Try this formula in A11

=OFFSET($B$4,COLUMNS($B$4:B4)-1,ROWS($B$4:B4)-1)

copy across and down to C20

regards, barry
0
 
ekaplan323Author Commented:
Barry,

It works, one question though, how come the column needs to be so wide so that the ##### don't show?

Thanks for the quick response,

Eric
0
 
barry houdiniCommented:
I think that just happens when you copy the formula from here - it can have some odd effects - it might be work deleting the cell completely and entering the formula manually.....

barry
0
 
ekaplan323Author Commented:
Barry,

Thanks, works on a new worksheet.

Eric
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now