Setting up a drop down box with sort criteria

[GingerSnapsToo]

I inherited a workbook with drop down lists but they're set up to take every
entry in a column, then sort by 'all', 'sort ascending', 'sort descending',
then all of the entries. When you click one of the entries, it takes you to
that entry cell and displays the entire line. If it's an entry that has been
used multiple times, like the sentence 'Process with normal benefits' used in
6 different cells in a single column, and it pulls all 6 lines, combining
them into one search.

This is brilliant, I love it! I just can't figure out how to set up my own
column with a drop down like that. Someone help, please?

 

[Dave Peterson]

Select your range (all the columns and include all the rows--including the
header row).

Then (in xl2003 menus)
Data|filter|autofilter

ps. Debra Dalgleish has lots of info about autofiltering:
http://contextures.com/xlautofilter01.html

 

[GingerSnapsToo]

Well that's closer but the only problem is the first column is just states
and the size of the cell varies from state to state. Alabama has 2 entries,
Arizona has 7 so the cells have been expanded to include the number of rows.
When using the auto filter and selecting the state, it just shows the state
but none of the rows next to the state.

Does that make sense?

 

[Gord Dibben]

Makes sense to me that only one Arizona will show because you have only one cell
with Arizona

If you have seven rows then you must have seven Arizonas, each in a single cell.

How did you "expand" the cell to include seven rows?

Did you merge A1:A7 into one cell?


Gord Dibben MS Excel MVP