Transpose with linked data
 

I have worksheet #2 containing a table where all cells, including row and
column headings, contain links to values in worksheet #1 in the workbook. I
want to "transpose" the table in #2 (including row and column headings) into
a table in worksheet #3, so that the rows in #2 become columns on #3 and the
columns in #2 become rows in #3, while maintaining the links to values in #1.
How do I do this.
I need a command functionally equivalent to
copy/paste special values/transpose/link
but in paste special values I can choose either transpose or link, but not
both.
Bill

Transpose with linked data
 

2 Steps:

1. On worksheet #3 highlight the transpose range, type:
=transpose(range2) and press cntrl&shift +Enter.

range2= the range of the table on worksheet #2 to be transposed.

2. Put your feet up on the table and smile


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531894

Transpose with linked data
 

One way:

Select the range to copy
Edit|Replace
what: = (equal sign)
with: $$$$$
replace all

Now your formulas are all text.

With that range still selected
edit|copy
then select the new location
edit|paste special|transpose

Now select each range
edit|replace
what: $$$$$
with: =
replace all

And you've converted the text back to formulas.

(Don't forget to do both the original range and the pasted range.)

Bill wrote:

I have worksheet #2 containing a table where all cells, including row and
column headings, contain links to values in worksheet #1 in the workbook. I
want to "transpose" the table in #2 (including row and column headings) into
a table in worksheet #3, so that the rows in #2 become columns on #3 and the
columns in #2 become rows in #3, while maintaining the links to values in #1.
How do I do this.
I need a command functionally equivalent to
copy/paste special values/transpose/link
but in paste special values I can choose either transpose or link, but not
both.
Bill

--

Dave Peterson

Transpose with linked data
 

Very nice Dave


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531894

Transpose with linked data
 

John James - thanks for the solution - knew it should be easy but could not
figure it out on my own.
--
Bill


"John James" wrote:


2 Steps:

1. On worksheet #3 highlight the transpose range, type:
=transpose(range2) and press cntrl&shift +Enter.

range2= the range of the table on worksheet #2 to be transposed.

2. Put your feet up on the table and smile


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531894

Transpose with linked data
 

Dave

Thanks for your suggestion -- this actually solved an additinal delima for
me at the same time.
--
Bill


"Dave Peterson" wrote:

One way:

Select the range to copy
Edit|Replace
what: = (equal sign)
with: $$$$$
replace all

Now your formulas are all text.

With that range still selected
edit|copy
then select the new location
edit|paste special|transpose

Now select each range
edit|replace
what: $$$$$
with: =
replace all

And you've converted the text back to formulas.

(Don't forget to do both the original range and the pasted range.)

Bill wrote:

I have worksheet #2 containing a table where all cells, including row and
column headings, contain links to values in worksheet #1 in the workbook. I
want to "transpose" the table in #2 (including row and column headings) into
a table in worksheet #3, so that the rows in #2 become columns on #3 and the
columns in #2 become rows in #3, while maintaining the links to values in #1.
How do I do this.
I need a command functionally equivalent to
copy/paste special values/transpose/link
but in paste special values I can choose either transpose or link, but not
both.
Bill

--

Dave Peterson

Transpose with linked data
 

Bill,

Your feedback is appreciated.

Cheers,


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531894