# SORTING RANGES OF CELLS USING ONE CELL AS THE CRITERIA

This is a discussion on SORTING RANGES OF CELLS USING ONE CELL AS THE CRITERIA within the Excel Questions forums, part of the Question Forums category; Hi every one i have 13 sheets in my workbook the first 12 contain the averages of the 12 players ...

1. ## SORTING RANGES OF CELLS USING ONE CELL AS THE CRITERIA

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

2. ## Re: SORTING RANGES OF CELLS USING ONE CELL AS THE CRITERIA

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.

******** ******************** ************************************************************************>
 Microsoft Excel - Book3 ___Running: xl2000 : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 A1B1C1D1E1G1A2B2C2D2E2G2A3B3C3D3E3G3A4B4C4D4E4G4A5B5C5D5E5A6B6C6D6E6A7B7C7D7E7A9B9C9D9E9A10B10C10D10E10A11B11C11D11E11A12B12C12D12E12A13B13C13D13E13A14B14C14D14E14A15B15C15D15E15 =

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

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•