Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transpose and link Multicolum matrix in 2 colum array
Hi
I have a matrix of the format ny chicago beijing ... manager 1 2 3 staff 101 200 300 Volunteer 102 202 302 and I want to link it to a single column data manager ny 1 staff ny 101 Volunteer ny 102 manager chicago 2 staff chicago 200 Volunteer chicago 202 where the 2nd table is linked to the first and whenever the data changes it in the first it changes also in the second |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transpose and link Multicolum matrix in 2 colum array
One play to try ..
Assuming the source matrix is in Sheet1, A1:D4 ny chicago beijing ... manager 1 2 3 staff 101 200 300 Volunteer 102 202 302 In a new Sheet2, place In A1: =INDEX(Sheet1!A:A,MOD(ROW(A1)-1,3)+2) In B1: =INDEX(Sheet1!$1:$1,,INT((ROW(A1)-1)/3)+2) In C1: =OFFSET(Sheet1!$B$2,MOD(ROW(A1)-1,3),INT((ROW(A1)-1)/3)) then select A1:C1 and fill down to C9 Sheet2 will auto-return the required results (dynamic to Sheet1's source matrix) Adapt to suit .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "stratis" wrote in message ... Hi I have a matrix of the format ny chicago beijing ... manager 1 2 3 staff 101 200 300 Volunteer 102 202 302 and I want to link it to a single column data manager ny 1 staff ny 101 Volunteer ny 102 manager chicago 2 staff chicago 200 Volunteer chicago 202 where the 2nd table is linked to the first and whenever the data changes it in the first it changes also in the second |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I transpose a paste link? | Excel Discussion (Misc queries) | |||
How can I transpose a paste link? | Excel Discussion (Misc queries) | |||
Transpose and link at the same time? | Excel Discussion (Misc queries) | |||
How do i past link and transpose simultaneously? | Excel Discussion (Misc queries) | |||
how do I transpose and paste a link in excel | Excel Worksheet Functions |