Advanced Filter ...??

[RWilliams]

Help please …

Scenario - I have a number of workbooks that have links to a ‘master’ to
manage days spent on about 20 projects. To make entering of relevant data
easier for the ‘Team leaders’ I would like to display only the projects that
relate to the team members even if they have not yet allotted time to it – ie
from below this would be P1 & P2 .

The table below is a very simplified version of the data, there are a number
of links both to and from the ‘master’ and a number of filters already
applied … I think I can do this with a formula in an ‘Advanced Filter’ but
cannot work out how to display the rows that contain no data for some of the
team members – I have also tried with ‘If’ statements and ‘lookup’ but I
think I am making it more complicated than it is … any thoughts would be
gratefully received …

Name Project Week 12 Week 13 Total
Mr A P1 3 3 6
P2 2 2 4
P3 0
Ms B P1 2 2 4
P2 0
P3 0
Mr C P1 0
P2 3 3 6
P3 0

Thanks in advance …

 

[bj]

if this is to be a display only table, a pivot table would be a good choice,

If it is to be an interactive table where people would enter data and it
would go back to the other tables, We need more information how do you
determine that there is a project assigned to the individual if he or she has
no time involved.

 

[RWilliams]

they are interactive tables that each Team Manager updates at least once a
week - this updates the master, which updates a couple of reports for the
project managers(and finance) .. the team manager assigns the most
appropriate member of his team to the next stage in a project, or it might go
to another team, depending on the stage requirements

some teams only work on one or two projects, depending on their specialism,
so a manager might currently be scrolling through a list of 20 projects for
each of maybe 10 members of his team - there are new projects starting every
month or so and current projects being closed .. there is currently a filter
applied to display all 'Active' and 'Complete' projects - the list of project
names and whether they are active, complete or inactive comes from the master
workbook which i maintain

I hope this makes sense and is in enough detail - very difficult when i know
what i am trying to achieve but you don't ...

 

[bj]

the simplest way to do this might be to use auto filter
have every project in a list for each individual

set your master sheet witha new column after Project called "assigned"
enter a 1 (or something) in this column for the projects assigned to each
individual.
set auto filter on this column to display non blanks.
by having auto filter on the other columns it wuld be easy to determine what
projects an individual is assigned to as the only display or to see who is
assigned to a given project without disurbing the rest of the sheet.

 

[Yogi Anand--www.energyefficientbuild.com]

Hi Ruth:

If I understand you correctly, you can use the following computed criterion
....

=Total=0

Now before you do AdvancedFilter ... you would want to have all the cells in
column A populated with the ProjectLeaders names for the output to be
meaningful. You can do this easily by selecting column A, then selecting all
the blank cells and copying the cell above and Pasting it with CTRL+ENTER.