Transpose and link Multicolum matrix in 2 colum array

[Guest]

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

 

[Max]

One play to try ..

Assuming the source matrix is in Sheet1, A14

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

---