I have several tables of data one above the other, each with the sam
columns. there are some blank cells. A copy of the workbook im usin
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) an
list all the rows that 'contain' the selected location.

Autofilter almost does what i need. The problem is that in some of th
location cells, there is more than one location. For example at th
top of the "track related projects" table in the sample i provided
there is "High Barnet, Totteridge". autofilter picks it up as exactl
that, but i need it to pick it up as "high barnet" AND "totteridge" s
that if a user selects EITHER high barnet or totteridge from the list
it brings up all the rows from all the lists that contain those tex
strings in the location column. I have no idea about writing th
code/function that would give me a filtered list like autofilter doe
but list the rows properly. It is possible to select 'custom' whe
using autofilter on the location column, and then use "contains" an
type in the item you want, but then anyone that uses it is going t
have to do this ALL the time to guarantee the correct result i
produced, or risk having missing rows, so this isn’t good. So someho
producing the list without autofilter is the only way to eliminat
these problems I think. I also really need to use a combobox where
can resize the fonts in it so that users can read the text (because th
chart the box will be placed on is extremely large and must be zoome
out to view, and the autofilter text is not resizable and its so tin
you cant read it when zoomed out). If the filtering can be done b
somehow manipulating the autofilter then this will probably be easies
and the font size problem doesn’t really matter, but i dont think that
possible.

Because the items in the dropdown list (i.e. the station names) wil
never change, I have made a list of exactly what text needs to b
searched for in the location column when each item in the list i
selected, and so can populate the combobox with these items, this lis
is on “sheet1” of my example. What I cant do is when clicking one o
the items in the dropdown, make it search the location column an
produce the results. I can easily define the area that needs to b
searched by highlighting the area on the sheet that contains the table
to be searched and then naming this area searcharea or somethin
similar, then tell it to search the relevant column in this searchare
and return every row that contains the given text. Except I need th
function to do this and to know how I make it run this function when
user clicks one of the items in the list. Also the rows that ar
returned need to keep their current formatting because they are colou
coded so that users know what table they have come from (the othe
possibility is having the function produce the list with the relevan
titles for each section as well, ie. For rows that came from th
‘station related projects’ table, put them under a station relate
projects title in the newly filtered list, and the same for rows fro
the other sections so that people can see where each row came fro
rather than relying on the colour coding to work it out). Users o
this will only have read access, only me and 1 other person will b
able to edit any of the data.

Ideally after the above is working it would also be very useful to b
able to sort the remaining list by order of the start date column wit
earliest dates at the top, and blank dates at the bottom. I trie
using the autofilter and then data/sort option and it actuall
rearranged the data in the tables themselves rather than JUST sortin
the current list so it ended up moving things around in the differen
tables which is obviously no good, and I really need to automaticall
sorted rather then going through menus. This is obviously far les
important than getting the first filtering part to work though.

If anyone could have a look at the sample i liked to above and work out
how to do this it would be most appreciated as i have no idea and dont
know vb very well at all. Im guessing theres probably things that can
do exactly this already since I imagine it’s a fairly common problem,
its just the few specific things I need that stop this being as simple
as using autofilter.

Thanks


---
Message posted from http://www.ExcelForum.com/