Hi, On my complicated excel spreadsheet I have names of clients in column A
say starting from A5. Names of staff across horizontally starting in J4.
Below the names of people a tick symbol has been typed in to indicate who's
working on which client. There are gaps between groups of people. When I
click on Autofilter, it doesn't seem to recognise the date below. I do have
various subheadings above the staff names and merged cells. What I am trying
to achieve is when I select a staff from the filter it should show me all
clients that staff is responsible for but its not picking this up. Can
someone help?

On Apr 17, 11:31 pm, dctalk wrote:
Hi, On my complicated excel spreadsheet I have names of clients in column A
say starting from A5. Names of staff across horizontally starting in J4.
Below the names of people a tick symbol has been typed in to indicate who's
working on which client. There are gaps between groups of people. When I
click on Autofilter, it doesn't seem to recognise the date below. I do have
various subheadings above the staff names and merged cells. What I am trying
to achieve is when I select a staff from the filter it should show me all
clients that staff is responsible for but its not picking this up. Can
someone help?

Usually, to operate any Filter properly, you need contiguous data.
That is, no empty rows or columns between your labels and data.
Although, if you manually define the database, you may be able to get
around this.

Also, I find that Merged cells tend to throw column and row actions
off, or make them impossible. If you can get by without them, skip it.
I use Center Across when I really need the centering, and avoid the
full Merge format.

This should work:
Select the whole area (which manually defines the database), including
both your horizontal and vertical labels. So that's A4:AA100 or
wherever the lower right edge is. Then turn on Autofilter and attempt
to filter on the appropriate column. But with your subheadings and
formatting and whatnot, it may be messy.

You may want to look into alternatives, like forming your data more
contiguously. The extra grouping or totalling that you want could be
done elsewhere, with references to the database. Actually, so could
the list of clients for each staff member, perhaps as an advanced
filter instead of autofilter.