View Single Post
  #5   Report Post  
Max
 
Posts: n/a
Default

Another way is to OFFSET ..

Assuming the source table is in Sheet1, in A1:C5

In Sheet2:

Put in A1:

=OFFSET(Sheet1!$A$1,COLUMNS($A$1:A1)-1,ROWS($A$1:A1)-1)

Copy A1 across as many cols as there are rows to extract from
Sheet1, viz. across to E1, then fill down by as many rows as there are cols
in Sheet1, viz. down to E3

Overshooting the copying across and down from A1 a little doesn't really
matter, all you'll get are zeros indicating the "boundaries" of the table in
Sheet1. And we could actually leave it as-is, to cater for possible
expansion in the table in Sheet1 <g

A matter of personal pref, of course, but I do find using OFFSET simpler to
quickly build a dynamic transpose of 1 sheet in another, compared to using
TRANSPOSE which requires array-entering and a precise selection of the
converse grid size in the destination sheet.

For a cleaner look, we could suppress extraneous zeros from showing in
Sheet2 via clicking:
Tools Options View tab Uncehck "Zero values" OK
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"mango" wrote in message
...
hi all,
may i know how to link cells/ranges from 1 sheet to another by column to

row?
thanks alot

for example, from this table 1
salary epf
dept 1 500 12
dept 2 670 43
dept 3 456 44
dept 4 344 22

to this table 2
dept 1 dept 2 dept 3 dept 4
salary 500 670 456 344
epf 12 43 44 22

when there is a change in dept1 salary(500) in table 1 then salary dept1
(500) in table 2 will change as well. transpose only paste value and not
link.