Filters

[dazoloko via OfficeKB.com]

Hi All

Im hoping someone can help.

I have a spreadsheet containing Training Information. A simplified version is
laid out below :-

The rows contain the Staff Name and Service Area and the and the Columns
contain the courses available. If the course is required by the staff member
a priority Letter is plotted in to the matrix
eg Darren requires Course 1 on a Priority A.

Now heres the tricky bit !! What I want to be able to do is filter on both
rows and columns so if i filter on Finance it will return Darren and Johns
Info but only show courses 1 and 2 and not course 3 as neither of those
require that.

Likewise if I filter on Darren it will only show Course 1

Name Service Area Course 1
Course 2 Course 3
Darren Finance A
John Finance A
A
Alan Admin A
A
David Admin A

Is this possible and if so how do I do it, Ive started to look at advanced
filters but not sure iif this is the way forward !!

Many Thanks

D

 

[Herbert Seidenberg]

In your Excel 2007 Table, insert another column
with this formula:
=--(COUNTIF(Table1[[#This Row],[Course1]:[Course3]],"A")>0)
Filter on that column and either Service Area or Name.