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

K

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?
 
D

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
 
K

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?
 
D

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).
 
S

ShaneDevenshire

Hi,

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

This assumes that A25:D55 is part of a larger range A1:D1000 for example.
 
K

Katalio

Perfect, thanks everyone!

ShaneDevenshire said:
Hi,

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

This assumes that A25:D55 is part of a larger range A1:D1000 for example.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top