A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

how to link from column to row?



 
 
Thread Tools Display Modes
  #1  
Old March 10th 05, 06:49 AM
mango
external usenet poster
 
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.


Ads
  #2  
Old March 10th 05, 07:43 AM
Arvi Laanemets
external usenet poster
 
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<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  
Old March 10th 05, 07:54 AM
Biff
external usenet poster
 
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.

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  
Old March 10th 05, 07:57 AM
OJ
external usenet poster
 
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  
Old March 10th 05, 10:02 AM
Max
external usenet poster
 
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 <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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 08:11 PM.


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