Results 1 to 2 of 2

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. #1
    Board Regular kevin lazell's Avatar
    Join Date
    Jun 2004
    Location
    ADDLESTONE,SURREY
    Posts
    446

    Default 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. #2
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,632

    Default 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
    =

    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.
    "Fair Winds and Following Seas"

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com