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

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" <stratis@discussions.microsoft.com> wrote in message
news:41ECAF29-9E97-46BF-B74A-DB442A08023B@microsoft.com...
> 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
>