Transpose with linked data

[Bill]

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

[John James]

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

[Dave Peterson]

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

[John James]

Very nice Dave

[Bill]

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
>
>

[Bill]

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
>

[John James]

Bill,

Your feedback is appreciated.

Cheers,