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.

Hi


Select somewhere a range like F1:J3
Enter the formula like
=TRANSPOSE(A1:C5)
or, when the reverted table will be on another sheet
=TRANSPOSE(Sheet1!A1:C5)
Press Ctrl+Shift+Enter (this enters the formula as an array formula)
That's all

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


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

Hi!

Here's one way:

Assume table 1 is in Sheet1 A1:C5.

Table 2 will be in Sheet2 A1:E3.

CopyPaste SpecialTranspose the column and row headers so
that:

Sheet2 A2 = Salary
Sheet2 A3 = EPF

Sheet2 B1:E1 = DeptX

In Sheet2 B2 enter this formula:

=OFFSET(Sheet1!$A$1,MATCH(B$1,Sheet1!$A$2:$A$5,0), MATCH
($A2,Sheet1!$B$1:$C$1,0))

Copy across to E2 then down.

Biff

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


.

Well, your question is ambiguous but if you want a link then use an
Array-entered (Ctrl+Shift+Enter) TRANSPOSE function. To do this
highlight the range in table 2 and enter "=TRANSPOSE(Sheet1!A1:A5)" and
press Ctrl+Shift+Enter.
Hth,
OJ

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.