Excel - Transpose and Paste Link

Asked By Kermi on 11-Jan-08 03:43 PM
I would like to be able to copy a block of cells, and in paste special I need
to be able to transpose and paste link. When I check transpose, I cannot
click paste link.  I know a pivot table will do this.  Is there an easier way?

ShaneDevenshir replied on 11-Jan-08 05:38 PM

You can use a formula approach:


a second way is to use a formula like:


In this case you select the range say M1:AH1, type the formula and press
Shift+Ctrl+Enter.  The array formula will do the job nicely.  These
techniques can be used with 3-D ranges.

Assume that you want to copy column D's data to the right and maintain a
formula.  In this example, I am only dealing with one column and the first
cell D1 contain a title which I am not transposing, although you could.

Shane Devenshire
Dave Peterson replied on 11-Jan-08 06:40 PM
I cheat.

I select the range to copy
and paste special|links to an unused area

Then I change those formulas to strings:
Select that pasted range
what: =        (equal sign)
with: $$$$$=
replace all

Then I can copy and paste|special transpose
Then fix this newly pasted area
Select the range
what:  $$$$$=
with:  =
replace all

And then delete that intermediate pasted range.


Dave Peterson