Autosort in Excel

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!

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


"Riley Snyder" wrote:

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!

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

"Riley Snyder" wrote:

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!