Excel Help - Can you sort a column by using formula (not macro)

[wca]

I am setting up a series of Excel sheets for my boss, in which one tab gets data from a previous tab. What I am trying to do is find a way for this new column (in the second tab) to be automatically sorted (it is a list of email, so alphabetically). This would than feed into another Excel sheet. I don't want my boss to have to go into that intermediary sheet and click sort himself or have to run a macro. Basically, is there any way to sort an entire column by utilizing formulas. I don't care if I have to create one or many extra columns. Thank you very much. Dan

 

[roseair9]

I may not be on the right track here, but for me, and my technically challenged bosses, the easiest thing to do is to setup autofilter. if you are using MSOffice 2003 or later it allows you to sort ascending or descending order by clicking the drop-down arrow and scrolling up on the options and selecting the sort order, this also allows for custom sorts, ie you are looking for only the contact details of all the members of a certain company. when i first started with this it seemed awkward but over time even my bosses have come to enjoy this particular feature of MSOffice 2003 and later. the older versions don't allow you to do the sort function on the auto-filter button. otherwise write a macro that runs when you open the spreadsheet.

 

[DelJo63]

I don't see any Insert FX() for SORT.

Also, sorting a column which contains a function value usually results in #REF ERRORs
(but an email address is just alphanumeric).

I suggest you maintain the spreadsheet and presort sheet2 before you distribute it.

 

[subcan]

I love to make 'useful' spreadsheets in excel, but some things that would be nice to do just are not realistic without some serious knowledge in VBA and advanced excel application creation.

What you are describing sounds a little bit confusing (without seeing the actual excel file). Sounds like your boss is more likely to mess things up by 'attempting' to sort columns him/herself. I have had many bosses like this. They know the basics to excel but without fully understanding how the spreadsheet works (regarding formulas and data flow) they are more likely to erase a formula, or put data in the wrong spot, etc... What I always do is lock (usually without a password) all cells that do not require input from the user (cells that do not have formulas). I also put easy to see buttons on the sheet that sort data, print, save, etc... Yes they can go to the menu and do this stuff too, but then they get lost and frustrated...

From my understanding of your needs, it sounds like you, or others, input the data and your boss just accesses the data. If this is the case I would likely make a simple access application that would get its data from your excel file and create reports for him depending on various queries. This is a little bit more difficult but does make it easier for the boss. You can also add many more features to the access database too, such as customer contact lists, etc...

I did do some research though to see if what you wanted to do was easily accomplished from within excel, and I could not find anything that was a simple fix, but as 'joebeard' suggested: Preformatting the data for your boss would be the easiest solution, but just repetative.

I have included the following extract (below) from:

Definitive Guide to Excel VBA, Second Edition
by Michael Kofler

This shows using andvanced filters to accomplish what I think you are trying to do, but like 'joebeard' said presorting is the easiest method. I hope that I have not just frustrated your quest for an answer with a rabbit trail. Good luck. If you come up with an answer, I would love to hear it.

SUB

Copying and Deleting Advanced Filters
If you wish to copy filtered data to another location in the table, you must select the option Copy To Another Location in the Advanced Filter dialog and specify an output range. The output range must be headed (that is, with column heads). At first glance this may seem like unnecessary additional labor, but in fact, it makes possible the selective copying of particular columns of the database. Much more burdensome is the restriction that the output range must be located in the currently active worksheet. It would often be worthwhile (and more readable) to copy the filtered data into another worksheet. In fact, that is possible, but Data|Filter|Advanced Filter must be executed while another worksheet is active.

In the example file an output range is defined, beginning with cell T23, that contains only the three columns giving first name, last name, and telephone number. If you would like to try out the copy command, place the cell pointer in the database, select the command Data|Filter|Advanced Filter, and in the dialog activate the copy mode. Then give a criteria range of "Filter1" as above (but you must also check the option Copy To Another Location and insert this location in the Copy To field) andx a "Copy to" range of T23:V23. (See Figure 11-8.)


Figure 11-8: The result of copying with an advanced filter
Unfortunately, there is no analogous copy command for autofilters. If you wish to copy selected data records via an autofilter, you must manually select and copy them. Selectively copying only certain columns is achieved only with extra effort—by hiding columns in the database.