Remember Me?

#1
March 10th 05, 06:49 AM
 mango Posts: n/a
how to link from column to row?

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

#2
March 10th 05, 07:43 AM
 Arvi Laanemets Posts: n/a

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

#3
March 10th 05, 07:54 AM
 Biff Posts: n/a

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

.

#4
March 10th 05, 07:57 AM
 OJ Posts: n/a

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

#5
March 10th 05, 10:02 AM
 Max Posts: n/a

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Susan Excel Worksheet Functions 0 February 16th 05 06:01 PM M. Frazel Excel Discussion (Misc queries) 1 January 14th 05 05:17 PM Rasoul Khoshravan Azar Excel Discussion (Misc queries) 1 December 18th 04 11:51 PM Rasoul Khoshravan Azar Excel Discussion (Misc queries) 1 December 17th 04 09:07 PM Rasoul Khoshravan Azar Excel Discussion (Misc queries) 1 December 17th 04 07:44 PM

All times are GMT +1. The time now is 06:40 AM.

The comments are property of their posters.