Trying to automate a SORT routine

Is there a way to automate a basic sort (i.e., designate a range of rows or a worksheet, a column to sort on, and ascending or descending order, and have the resulting list generated/updated automatically)? This is one of many steps in creating a report and I'm trying to reduce the number of manual steps that need to be done.

Example:
Sheet1 is raw data
Sheet2 are basic statistical calculations
Sheet3 copies stats into a layout to print and sorts in order
Current sort process:
- select rows 30-50
- select Data menu -> Sort
- select Column D & Descending
- Sort

Works great except that we have 6 sheets that need to be sorted before printing. My goal is for all the sorting to occur automatically to reduce user error.

I'd really appreciate any thoughts or suggestions.

You can sort using the RANK() function in conjuction with either the LARGE() or SMALL() functions and the INDEX() function.

I have just tried to construct a sort using my own advice...

What I came up with is:
F30 =RANK(D30,D$30:D$50) [replicate down to F50]
G30 =MATCH(ROW()-29,F$30:F$50,0) [replicate down to G50]
H30 =INDEX(A$30:A$50,$G30) [replicate across and down to K50]

OK so I used MATCH and not LARGE or SMALL, thats what comes of submitting a reply before trying it out.

Mark.

Originally Posted by Mark@Work

I have just tried to construct a sort using my own advice...

What I came up with is:
F30 =RANK(D30,D$30:D$50) [replicate down to F50]
G30 =MATCH(ROW()-29,F$30:F$50,0) [replicate down to G50]
H30 =INDEX(A$30:A$50,$G30) [replicate across and down to K50]

OK so I used MATCH and not LARGE or SMALL, thats what comes of submitting a reply before trying it out.

Mark.

Mark,

I can't get this to work. Would you be able to give it a shot with a sample file (attached). I think I'm messing up the data references. On this sample, rows 8-40 are being sorted by column D.

Dan