Is it possible to automatically sort cells easily? (I'm going mad!)

Hi all

My problem is this... 8o0

I need to sort the information contained within a hidden table (4 rows by 8
columns) and display the results in a second table that is visible.

The sort is based on numerical values (descending) in the 8th column of the
hidden table - the results of the sort need to be refined further by values
in the 7th column if those in the 8th column are equal.

But... I need the display table to rearrange itself automatically as the
values in the hidden table change.

In short, I need it to work exactly as the sort button on the toolbar - but
without my intervention!

In case you're wondering, it's to sort a football league table as the
results of the matches come in.

Thanks in advance

Andy

Andy,

If you want to do that using formulas, then you need to add 2 helper
columns to your original table - the first will return the rank of the
value of the second, which must contain some formula that returns a
number or other value that you can sort on. An example will help.

Suppose you have

1 10
3 6
1 5
2 11

and want to sort based on the first column and tie-break on the
second. Add two columns - let's say A and B, so that your data is now
in C and D. In B1, use the formula

= C1 + .00001*D1 +row()*.000000001

This formula must be designed so that the resulting values are always
properly scaled, and will sort the same as if you used multiple column
sorting. In this example, I multiply the second column by .00001, so
that the resulting number is always less than the next larger value in
column C. The row() is used as a tie-breaker.

In A1, use the formula

=RANK(B1,$B$1:$B$4)

and copy down to A4.

Now to make your auto-sorting table.

In cell F1, use the formula
=VLOOKUP(ROW(),$A$1:$D$4,3,FALSE)
and in G1,
=VLOOKUP(ROW(),$A$1:$D$4,4,FALSE)

and copy down for a total of 4 rows.

If your table doesn't start on row 1, then you need to use something
like this, where your table starts in cell F11:
=VLOOKUP(ROW()-ROW($F10),$A$1:$D$4,3,FALSE)

And as the values in your data table change, your resulting table will
re-sort automatically.
HTH,
Bernie
MS Excel MVP

"Andy Sandford" wrote in message
...
Hi all

My problem is this... 8o0

I need to sort the information contained within a hidden table (4
rows by 8
columns) and display the results in a second table that is visible.

The sort is based on numerical values (descending) in the 8th column
of the
hidden table - the results of the sort need to be refined further by
values
in the 7th column if those in the 8th column are equal.

But... I need the display table to rearrange itself automatically as
the
values in the hidden table change.

In short, I need it to work exactly as the sort button on the
toolbar - but
without my intervention!

In case you're wondering, it's to sort a football league table as
the
results of the matches come in.

Thanks in advance

Andy

Bernie

Thanks for your help - that was right on the money!

It took a little figuring out to apply to my table. But once I could see
what you were doing, it was a piece of cake!

Thanks again

Andy

"Bernie Deitrick" wrote in message
...
Andy,

If you want to do that using formulas, then you need to add 2 helper
columns to your original table - the first will return the rank of the
value of the second, which must contain some formula that returns a
number or other value that you can sort on. An example will help.

Suppose you have

1 10
3 6
1 5
2 11

and want to sort based on the first column and tie-break on the
second. Add two columns - let's say A and B, so that your data is now
in C and D. In B1, use the formula

= C1 + .00001*D1 +row()*.000000001

This formula must be designed so that the resulting values are always
properly scaled, and will sort the same as if you used multiple column
sorting. In this example, I multiply the second column by .00001, so
that the resulting number is always less than the next larger value in
column C. The row() is used as a tie-breaker.

In A1, use the formula

=RANK(B1,$B$1:$B$4)

and copy down to A4.

Now to make your auto-sorting table.

In cell F1, use the formula
=VLOOKUP(ROW(),$A$1:$D$4,3,FALSE)
and in G1,
=VLOOKUP(ROW(),$A$1:$D$4,4,FALSE)

and copy down for a total of 4 rows.

If your table doesn't start on row 1, then you need to use something
like this, where your table starts in cell F11:
=VLOOKUP(ROW()-ROW($F10),$A$1:$D$4,3,FALSE)

And as the values in your data table change, your resulting table will
re-sort automatically.
HTH,
Bernie
MS Excel MVP

"Andy Sandford" wrote in message
...
Hi all

My problem is this... 8o0

I need to sort the information contained within a hidden table (4
rows by 8
columns) and display the results in a second table that is visible.

The sort is based on numerical values (descending) in the 8th column
of the
hidden table - the results of the sort need to be refined further by
values
in the 7th column if those in the 8th column are equal.

But... I need the display table to rearrange itself automatically as
the
values in the hidden table change.

In short, I need it to work exactly as the sort button on the
toolbar - but
without my intervention!

In case you're wondering, it's to sort a football league table as
the
results of the matches come in.

Thanks in advance

Andy