Automatically Filter and populate filtered data in next sheet

Hi ,

Attached is the excel sheet for my query

A report circulated to all managers to check data for their team .A manager can find the list of people who have completed a task and who havent without the task of having to filter manually for their department .I have got to the extent of creating a search box that when input with dept code automatically filters data for that department only.
The colums A to I will be hidden .Only visible from column J.

I can formulate the rest of the little things -Total num completed ,completion percentage etc.

I need help in this step - Is there a way to automatically populate data in an other sheet listing data only for those users from the filtered port who have "in progress" or "on hold"status in column o .
I used query in this case and it worked ,but realized later it doesnt work if an other user opens on an other desktop.Also query is increasing the size of the file massively.We have attachment restriction and would not be able to circulate this out if so big .

Please help

Thanks ,
Mammu

Hi Pete ,

Works wonderfully .But i also need the list of staff who are in "in progress" /"on hold" status ,basically anyone other than " completed "status to populate in the other sheet for the dept code entered ,because it is going to be a huge database and they dont want to filter the data individually for their teams .

So it would be one sheet with all data for that Dept code (sheet 3 which you provided already).
Another sheet with anyone not completed for that dept .

If the leader is handling 2 or more departments is there a way to choose more than one code in the search box?

Thanks much
Mammu.

One way in A5 of Result, copied across and down:

=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$F$3:$F$100={"on hold","in progress"},IF(Sheet1!$C$3:$C$100=$B$2,ROW(Sheet1!$F$3:$F$100))),ROWS(A$5:A5))),"")

This is an array formula. Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

This requires no helper columns. If you have Excel 2010 or later, an alternative non-array formula can be used... but your profile states 2007. I also changed the spelling of om hold to on hold!!

PL see file.
In A4 of Sheet3 and dragged across till column F

Please Login or Register  to view this content.