how to link from column to row?

[mango]

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.

 

[Arvi Laanemets]

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

 

[Biff]

Hi!

Here's one way:

Assume table 1 is in Sheet1 A1:C5.

Table 2 will be in Sheet2 A1:E3.

Copy>Paste Special>Transpose 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

 

[OJ]

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