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