Transpose tables sorted by performance order

[gotido]

I have 5 tables, which correspond to 5 supervisors:
supervisor 1: c7:w24
supervisor 2: c29:w46
supervisor 3:c51:w68
supervisor 4: c73:w85
supervisor 5: c90:w107

in the corner of the table is a value from 1 to 5 that identifies the order of the supervisors in their daily performance according to the objectives achieved

The value is in the following cells
supervisor 1: w7
supervisor 2: w29
supervisor 3: w51
supervisor 4: w73
supervisor 5: w90

I would like the 5 tables on the same sheet, starting from cell BA1, to appear transposed and ordered by the performance value of the supervisors from 1 to 5.

Imagine that one day Supervisor 1 is in third place, Supervisor 2 is in fifth place, Supervisor 3 is in first place, Supervisor 4 is in second place and Supervisor 5 is in fourth place, but the next day they change places.
How can I do this?

 

[jimrward]

ok here is a first attempt with a mini example, in the w7 cell put the rank and below that add 0.1 to the rank so you get 1,1.1,1.2,1.3 etc
for the dynamic performance table use INDEX and MATCH to look for 1,1.1,1.2 etc and then 2,2.1,2.2 etc and drag across for your data columns
try the example below changing the rank from 1 to 2 and back again and watch the table change, is this what you are after

Book1
	A	B	C	D	E	F	G
1	s1	jim	1		1	s1	jim
2	s1	fred	1.1		1.1	s1	fred
3	s1	bill	1.2		1.2	s1	bill
4	s1	dave	1.3		1.3	s1	dave
5	s1	steve	1.4		1.4	s1	steve
6
7
8	s2	anne	2		2	s2	anne
9	s2	lily	2.1		2.1	s2	lily
10	s2	kirsten	2.2		2.2	s2	kirsten
11	s2	kate	2.3		2.3	s2	kate
12	s2	izzy	2.4		2.4	s2	izzy
Sheet1

Cell Formulas
Range	Formula
F8:G12,F1:G5	F1	=INDEX(A$1:A$12,MATCH($E1,$C$1:$C$12,0))
E2:E5,C9:C12,E9:E12,C2:C5	E2	=E1+0.1