excel vba training - Sort & Filter by Date

The UK's most regular instructor-led training courses.
Training information: excel vba training · Excel advanced · Microsoft Excel Training
See also · excel-courses-london · excel courses in london · excel microsoft training

Resolved · Low Priority · Version Standard

1 post

ReplyTue 28 Nov 2006, 12:54Delegate Sachin said...

Sachin has attended:
Excel Advanced course

Sort & Filter by Date

Hi

I am using a spreadsheet to prioritse my work by (1) numerical priority 1-3 and (2) by date the job was recieved.

How do I make a parameter so that it is sorted / filtered by these two automatically?

Currently I am using autofilter across the heading names - however, I need to keep filtering each time I add/remove/edit a line.

Please can I find another way of doing this- perhaps by using some of the advanced options or funtion builder. Alternatively, I cannot make the "sort" function understand that I want the oldest date first.

Kind Regards
Sachin Kotak
sachin.kotak@autotrader.co.uk

Version	Date	Location	Places available	Book	Next place rate (£)		List price
Excel VBA Intro Intermediate 2 day course
Version	Date	Location	Places available	Book	Pay by Card	Pay by Invoice	List price
2007	Mon 9 + Tue 10 Aug 2010	Bloomsbury	0	FULL			£495
2003	Wed 11 + Thu 12 Aug 2010	Southwark	3		£495	£495	£495
2003	Wed 25 + Thu 26 Aug 2010	Bloomsbury	8		£450	£455	£495
2007	Thu 2 + Fri 3 Sep 2010	Bloomsbury	5		£490	£495	£495
2003	Wed 8 + Thu 9 Sep 2010	Southwark	8		£450	£455	£495
2003	Tue 21 + Wed 22 Sep 2010	Southwark	8		£450	£455	£495
Full Schedule: See all 47 Excel VBA Intro Intermediate course dates. Bookings currently available until 28th September 2011.

835 posts

ReplyThu 30 Nov 2006, 09:23Trainer Richard said...

RE: Sort & Filter by Date

You can acheive this by extending your filter area further down the page. This means that when you add new lines, they will be included.
So, select the area with your data, including headings, and an additional 50 lines.
Then autofilter.

The other aspect would be for you to setup a VIEW.
Click View, then Custom views.
This allows you to set specific autofilter settings, and then save that view, to that you can get back to the setting you want very quickly.
Once you have setup the custom views, you could also add a toolbar using:
Right-click Toolbars, Customise, and looking under the Toolbars tab and dragging the CUSTOM VIEWS label and box to an existing toolbar. This iwill allow you to scroll through the various views you have saved, on demand.

The concept you are talking about is related to database type functionality. In Access you could have a saved query that always sorts your list, in the view you want, no matter how much you add. You can acheive this in Excel, as described above.

· Formulas In Excel: The Very Basics
· Creating Smarter, Customized Invoices for your Small Business with Microsoft Excel
· How to Create Better Excel Spreadsheets: Part Two
· Correcting Numerical Glitches In Excel
· A Blinking Good Excel Tip

Excel tip:

Using Excel's MODE function

Use Excel's MODE function to display the most common value present in a particular range of cells.

The Mode function looks like this:

=MODE(cell range)

As an example, if 35 is the most commonly recurring number in a particular cell range, then the function will display 35.

View all Excel hints and tips