Autosort cells

[Marland]

I am trying to make a group of cells sort when data
changes in certain cells without using the sort function
every time this data changes. In the example below, I
want Column A to stay the same all of the time, then I
want the sort function to sort Column F from highest to
lowest, keeping Columns C-H in each row coresponding with
the original F cell. The values in Column F and H are
linked to another worksheet. When I enter these values
into the worksheet, save, open up the worksheet with this
example in it, and recalculate the data in the workbook,
I want this sort to take place. Can I do this?

Column
A C F G H
Row 12 1.) Team # 1 87 - 60
Row 13 2.) Team # 4 132 - 65
Row 14 3.) Team # 6 77 - 70
Row 15 4.) Team # 3 74 - 73
Row 16 5.) Team # 5 70 - 77
Row 17 6.) Team # 2 51 - 96

I want to acheive this.

Column
A C F G H
Row 12 1.) Team # 4 132 - 65
Row 13 2.) Team # 1 87 - 60
Row 14 3.) Team # 6 77 - 70
Row 15 4.) Team # 3 74 - 73
Row 16 5.) Team # 5 70 - 77
Row 17 6.) Team # 2 51 - 96

Thanks,
Marland

 

[Frank Kabel]

Hi
you may try the following (assumption your data source is on 'sheet2'
having the following layout:
A B C
1 Team#1 87 60
2 Team#2 51 96
column A will go to column C; col. B to column F (this will be sorted)
and col C to column H

If you don't have duplicate values in column B you may use the
following formula in column C of your target sheet (starting in C1):
=INDEX('sheet2'!$A$1:$A$20,MATCH(LARGE('sheet2'!$B$1:$B$20,ROW()),'shee
t2'!$B$1:$B$20,0),1)
copy down

in F1 insert the formula
=LARGE('sheet2'!$B$1:$B$20,ROW())
copy down

in H1 insert the formula
=INDEX('sheet2'!$C$1:$C$20,MATCH(LARGE('sheet2'!$B$1:$B$20,ROW()),'shee
t2'!$B$1:$B$20,0),1)
copy down


Note: You will get wrong results if there could be duplicate values in
column B of your source sheet. In this case you may use the following
array formula (entered with CTRL+SHIFT+ENTER) in C1:
=INDEX('sheet2'!$A$1:$A$20,MATCH(LARGE('sheet2'!$B$1:$B$20+ROW('sheet2'
!$B$1:$B$20)/10000,ROW()),'sheet2'!$B$1:$B$20+ROW('sheet2'!$B$1:$B$20)/
10000,0),1)

and adapt the other formulas accordingly