Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
mango
 
Posts: n/a
Default 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
link.


  #2   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

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.




  #3   Report Post  
Biff
 
Posts: n/a
Default

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.


.

  #4   Report Post  
OJ
 
Posts: n/a
Default

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
LINK ONE ROW BASED ON CONTENTS OF A COLUMN WITHIN THE ROW (DATE) Susan Excel Worksheet Functions 0 February 16th 05 06:01 PM
How do I link columns so data flows from 1 column to another like. M. Frazel Excel Discussion (Misc queries) 1 January 14th 05 05:17 PM
Remove link fr a column of entries Rasoul Khoshravan Azar Excel Discussion (Misc queries) 1 December 18th 04 11:51 PM
Remove link fr a column of entries Rasoul Khoshravan Azar Excel Discussion (Misc queries) 1 December 17th 04 09:07 PM
Remove link fr a column of entries Rasoul Khoshravan Azar Excel Discussion (Misc queries) 1 December 17th 04 07:44 PM


All times are GMT +1. The time now is 09:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"