automatically sorting without a macro
Posted by Aoife on December 06, 2001 2:18 AM
I have a spreadsheet in which numerous people record their tasks, each on a different worksheet.
Is there anyway to automatically sort records on a master sheet so that records of a certain type (i.e. "closed") will always appear on the top and all others beneath without using a macro?
Posted by Tom Urtis on December 06, 2001 4:11 AM
Unless someone develops a Telepathy UDF, you will need to perform some manual action or incorporate some type of mechanism to do the sorts. That means either manual action, formulas, or programming code.
It sounds as if your file is sizeable, and with "numerous people" on "different worksheets" as you say, you can bet that manual action will eventually lead to user error. The manual action in your case sounds like a filter on all the users' sheets for the criteria "closed", then copying the visible dynaset from each user page to your "master sheet". At the very least it would be to copy all the user sheets data onto the master sheet and manually sort by the "closed" field.
Depending on the size of your workbook, another alternative could be to place formulas on your master sheet to display the users' sheets data to simulate a sort, which will weigh down the file and bother your users. Plus that means protecting the formula'd cells which requires unprotecting the master sheet to sort it, then reprotecting it. Trusting a bunch of users to do that correctly every time is not a reasonable expectation.
If the reason why you don't want a macro is that you don't want to be bothered with triggering an event (such as clicking a button or maintaining a custom sort menu/bar/command button), then have a look at Aaron Blood's autosort example (download #5) on this link:
http://geocities.com/aaronblood/pages/vba.html
Good luck.
Tom Urtis
