Paste Link & Transpose?

[clintonjh]

Hi,

Does anyone know of a way to copy and paste as a link but transpose the data at the same time?

I need to copy a range of cells in a column and paste into another sheet as a row and have the row update if the column updates.

 

[Darth Metal]

1. Copy your range from sheet1
2. Paste Links into an unused region on sheet2
3. Use Find and replace to replace = with xx
4. copy formulas and transpose where you want the data
5. Use Find and replace to replace xx with =

 

[petes]

How do I do it the other way... i gave a try, but could not achieve it....

Transpose and link from A1:B1 to A1:A3


Thanks!!

 

[petes]

Any idea guys for my above question??

Thanks!!

 

[elmar007]

Re: Paste Link & Transpose? avoiding the 0

a fab forula but... How do I do this but avoid copying over 0 for blank cells

the issue I have is that it is copying over the ) but then when I do a counta it records them as if something is in that cell!![


any ideas?


cheers

=INDEX(Sheet1!$A$1:$A$5,COLUMN()-COLUMN($B$1)+1)

will transpose as desired. The COLUMN($B$1) bit refers to the formula cell where it is first put.

 

[Aladin Akyurek]

Re: Paste Link & Transpose? avoiding the 0

a fab forula but... How do I do this but avoid copying over 0 for blank cells

the issue I have is that it is copying over the ) but then when I do a counta it records them as if something is in that cell!![


any ideas?


cheers

Consider...

XAD	XAD	q	w	fad	jad	lad	25
q
w
fad
jad
lad
25

<tbody>
</tbody>

A1:A10 is the range of interest.

B1, control+shift+enter, not just enter, and copy across:

=IFERROR(INDEX($A$1:$A$10,SMALL(IF(1-($A$1:$A$10=""),
  ROW($A$1:$A$10)-ROW($A$1)+1),COLUMN()-COLUMN($B$1)+1)),"")

We often use nowadays

COLUMNS($B$1:B1)

instead of my old invention:

COLUMN()-COLUMN($B$1)+1

which probably as much efficient.

Note that IFERROR requires a 2007 bsystem or later. If you are on older system:

B1, control+shift+enter and copy across:

=IF(COLUMNS($B$1:B1)<=SUM(IF(1-($A$1:$A$10=""),1)),
  INDEX($A$1:$A$10,SMALL(IF(1-($A$1:$A$10=""),
   ROW($A$1:$A$10)-ROW($A$1)+1),COLUMNS($B$1:B1))),"")

 

[Aladin Akyurek]

How do I do it the other way... i gave a try, but could not achieve it....

Transpose and link from A1:B1 to A1:A3


Thanks!!

Any idea guys for my above question??

Thanks!!

Apparently, we missed this question...

JAD	NAD	KAD
NAD
KAD

<colgroup><col style="width: 48pt;" span="3" width="64"> <tbody>
</tbody>

A1:C1 houses the data which must be transposed to A1:A3...

In A2 enter and copy down:

=IFERROR(INDEX($B$1:$C$1,ROWS($A$2:A2)),"")