John Walkenbach has a technique that comes very close to what you want at:
http://j-walk.com/ss/excel/usertips/tip068.htm



DWeb wrote:

Hello! I'm not sure if this is the right Excel group to post this question
in, but I am hopeful that someone can point me in the right direction:

I have a worksheet where all the rows represent tasks and the columns are
named "Requirements", "Build", "Test", "Deploy". The intersection of the
rows and columns have a numeric value (hours).

I would like to move this data into a separate worksheet where there is a
row for each task, but where the columns from the original worksheet
("Requirements", "Design", "Build", etc.) are now repeated beneath each task
with the original numeric data (hours) in the adjacent column.

So, I have this:

Requirements Design Build
Task 1 8 5 10
Task 2 5 10 8

And want it to show up on another worksheet like this:

Task 1
Requirements 8
Design 5
Build 10

Task 2
Requirements 5
Design 10
Build 8

Does anyone have an example of a macro or worksheet functions that can do
this? Another point is that the original list will change, so I need to be
able to dynamically recreate the new worksheet as changes occur.

Thank you!

--

Dave Peterson