Autosort in Excel

[Riley Snyder]

Hi all,

I have a workbook with several sheets that link to one master sheet.
The master sheet (sheet 8) is the one I enter data into and it is set
up to automatically add the new data into the other spreadsheets
(sheets 1 and 2).

I now want each spreadsheet (sheets 1 and 2) to automatically sort the
new data by a specified column as I enter it in, without changing the
master sheet.

There is data in columns b through h, with b having the date. The
data I want sorted does not start until row 3 and does not have an
ending row.

One spreadsheet is sorted by date (column b), and the other is by
category (column g). I don't want to use a macro, so does anyone have
a code I can use for auto sorting by date for sheet 1, and by category
for sheet 2?

Thank you in advance!

 

[AFSSkier]

Riley,

Have you concidered doing pivot tables off your main pages? Pivot tables
are more reliable them calc formulas and/or code.

Debra Dalgleish has a great pivot table tutorial at:
http://www.contextures.com/xlPivot01.html

Kevin

 

[Michael R]

Riley,

Here is a method to auto-sort numeric data.

Example:
* We have a table of products and their weights (unsorted) and want a
result table showing the products and weights sorted by weight descending.

Assume:
* Column A = product codes (A3:A28)
* Column B = the product weights (B3:B28)

First Helper column "D" (Make sort criteria unique)
* Formula in D3 =B3+RAND()/1000000000
==> copy down to D28
==> The divisor must be large enough so the adding of the random number does
not interfere with the significant digits of the weight (your sort criteria).
==> The purpose of this is to create unique sort criteria; if we had a
couple of products with identical weights then the following formulae would
produce wrong results.

Second helper column E (Sort the criteria list)
* Formula in E3 =LARGE($D$3:$D$28,ROW()-2)
==> copy down to E28
==> Using the LARGE function results in a descending list. If you need an
ascending list, use the SMALL function.
==> The "-2" is necessary to correctly calculate k (the k'th element of the
list). Since in our example the first data element is on row 3 we need to
deduct 2 to create a 1 telling the LARGE function that we want the 1st
element.

Third helper olumn "F" (Offset of the sorted data element in the unsorted
list)
* Formula in F3 =MATCH(E3,$D$3:$D$28,0)
==> copy down to F28

The sorted Result Table
Column G: Product codes: Formula in G3 =INDEX($A$3:$A$28,F3) (copy down)
Column H: Weights: Formula in H3 =INDEX($B$3:$B$28,F3) (copy down)

Hope that helps,
Michael