How do I create a drop-down menu to sort data?

[Katalio]

Hi! I use Microsoft Office 2003. A part of my job is to maintain a
spreadsheet containing data on vehicles my company owns & rents. It looks a
little something like this:

unit no. --- VIN --- plate no. --- year --- make --- model --- cost
(data goes in cells underneath column titles above)

What I want to do is have a drop-down menu that will take all that data and
allow the user to sort its order of precedence in the list by unit number, or
year, or cost, etc. How would I go about doing that?

 

[Dave Peterson]

If you're using xl2003 (or higher), you can apply data|filter|autofilter.
There's an option under each arrow that allows you to sort the filtered range by
that field.

If you're using xl2002 or below (or any version of excel, really), you can use a
technique at Debra Dalgleish's site:

http://contextures.com/xlSort02.html

 

[Katalio]

That was actually a big help in pointing me in the right direction. The only
thing I would like to add is that on this page there are multiple charts
following that same format on the same page, stacked one over the other with
only a blank line separating each one. How do I keep the sorting contained to
a specific range of cells?

 

[Dave Peterson]

The code either sorts the autofilter range or determines the last row of the
range to be sorted by using:

LastRow = .Cells(.Rows.Count, strCol).End(xlUp).Row
(strCol is "A" in the sample code)

Any embedded chart won't change this list row.

If you have data in rows after the table to be sorted, then that would cause
trouble. But you haven't shared enough information to help (for me, at least).

 

[ShaneDevenshire]

Hi,

Not entirely clear what your data looks like, but to sort a specific range
in Excel, select the range, say A2555 and choose Data, Sort, specify the
field you want to sort on and then indicate that there is No Header Row.

This assumes that A2555 is part of a larger range A11000 for example.

 

[Katalio]

Perfect, thanks everyone!

Hi,

Not entirely clear what your data looks like, but to sort a specific range
in Excel, select the range, say A2555 and choose Data, Sort, specify the
field you want to sort on and then indicate that there is No Header Row.

This assumes that A2555 is part of a larger range A11000 for example.