Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-02-23
4
Medium Priority
?
246 Views
Last Modified: 2012-05-11
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
Comment
Question by:ekaplan323
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 34961937
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
 

Author Comment

by:ekaplan323
ID: 34962072
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 34962098
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
 

Author Closing Comment

by:ekaplan323
ID: 34962196
Barry,

Thanks, works on a new worksheet.

Eric
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question