SORTING RANGES OF CELLS USING ONE CELL AS THE CRITERIA

[kevin lazell]

Hi every one
i have 13 sheets in my workbook the first 12 contain the averages
of the 12 players in my team, the last sheet contains all the information for all 12 players.sheet13 cells A1:L7 contains the averages from sheet1
using a formula in each cell ie A1=SHEET1!$A$1and A2=SHEET1!$A$2etc through to L7=SHEET1!$L$7 the next row of cells ie A8:L8 is filled with a black dividing linethen the process starts all over again with sheet2 going in cells A9:L15,SHEET3 going in cells A17:L23 what i would like to do using highest result in cells L7,L15,L23
etc to swop around the ranges of averages ie A1:L7,A9:L15,A17:L23 so the highest scoring player goes to the top second highest next and so on
i hope i have made it clear enough for you to understand?
any help with this would be most appreciated Kev

 

[fairwinds]

Hi,

You could retrieve your data with indirect formulas that is getting the sheet name from a list like in this example.

The formula in G column should get you the average value from each sheet. If you sort G and H by G kolumn the other cells should adjust as you want.

These are volatile formulas, meaning your sheet could get slow. Try and see if it is acceptable.

Book3
	A	B	C	D	E	F	G	H
1	555	0	0	0	0		555	Sheet2
2	0	0	0	0	0		666	Sheet3
3	0	0	0	0	0		0	Sheet4
4	0	0	0	0	0		0	Sheet5
5	0	0	0	0	0			Sheet6
6	0	0	0	0	0			Sheet7
7	0	0	0	0	0			Sheet8
8								Sheet9
9	666	0	0	0	0			Sheet10
10	0	0	0	0	0			Sheet11
11	0	0	0	0	0			Sheet12
12	0	0	0	0	0
13	0	0	0	0	0
14	0	0	0	0	0
15	0	0	0	0	0
Sheet1