neowok

Try this for starters: Add an entry to the top of the list on Sheet1 that
reads (All). Put a combobox from the Control Toolbox on your sheet. Set
the ListFillRows property equal to the list on Sheet1!A1:A51. resize the
combobox and its font so that you can see the entries. Click on the View
Code button and insert this code

Private Sub ComboBox1_Change()

If Me.ComboBox1.Value = "(All)" Then
Me.AutoFilterMode = False
Else
Me.Range("A34:I132").AutoFilter 4, _
"=*" & Me.ComboBox1.Value & "*", xlAnd, , False
End If

End Sub

When you select (All) from the combobox, the filter is removed. Any other
entry creates and autofilter and sets the custom filter to 'contains'
whatever is in the combobox.

That won't help you with the sorting end of it. I think for that you will
need to use an Advanced Filter and filter to a different location. I
thought you should try this first and see if it's even close to what you
want. If it is and you want to explore the Advanced Filter option, let me
know.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"neowok " wrote in message
...
I have several tables of data one above the other, each with the same
columns. there are some blank cells. A copy of the workbook im using
can be found at http://www.darkcity.nildram.co.uk/listtest.xls

what i need to do is filter the entire lot based on the 'location'
column (which is always filled in each of tables I need to filter) and
list all the rows that 'contain' the selected location.

Autofilter almost does what i need. The problem is that in some of the
location cells, there is more than one location. For example at the
top of the "track related projects" table in the sample i provided,
there is "High Barnet, Totteridge".