If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




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. 
Ads 
#2




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




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




Well, your question is ambiguous but if you want a link then use an
Arrayentered (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




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 asis, 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 arrayentering 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 <at>yahoo<dot>com  "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. > > 
Thread Tools  
Display Modes  


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 05: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 04:17 PM 
Remove link fr a column of entries  Rasoul Khoshravan Azar  Excel Discussion (Misc queries)  1  December 18th 04 10:51 PM 
Remove link fr a column of entries  Rasoul Khoshravan Azar  Excel Discussion (Misc queries)  1  December 17th 04 08:07 PM 
Remove link fr a column of entries  Rasoul Khoshravan Azar  Excel Discussion (Misc queries)  1  December 17th 04 06:44 PM 