Need to Search for Value Across Multiple Sheets, Value Occur Multiple Times on Sheets

I have an excel file for planning work in the office. Each PM has a worksheet which list projects (each project is a row) with other various data on the project. So there are several worksheets. One of the columns in each sheet is for assigning work (i.e. let's say it contains "Bob" as the assigned person). Same column each sheet. But "Bob" may show up several times each sheet. Now I would like to develop a worksheet for "Bob" that is automatically populated with the project data (again, the data i want is always in the same row as "Bob") from all of the worksheets.

I've messed around with a combination of the index() and small() function (as an array function) and am successful in returning the data i want (while ignoring that which i don't want), but only from one sheet. Is there anyway to find the data assigned to "Bob" across all sheets and return it onto one sheet, all while ignoring those rows that don't concern "Bob"?

Leith,

I've attached the file. So want i want is the ability to "assign" projects to Bob in both the Dave S. and Tony tabs, and it automatically populate Bob's tab with the project information. The last tab was a previous attempt, but it only works with one PM sheet, not multiple.

The idea is that I need to go through each PM tab (there will be more than two) and select only those rows (projects) that are assigned to Bob.

Make sense? Thanks for your help.

I've cleaned up the file. The dates all align, and I've made the columns consistent. I apologize, the file I sent was sort of in the middle of construction. I wasn't intending to use the dates directly to reference, just the match up the relative column locations, but however it works is fine.

I've also gotten rid of the VBA function; it didn't work anyway.

And finally, the last sheet (ENG-Bob (index-small)) is thrown in as extra. It is a demonstration of what I would like to achieve, except that it only returns results from one PM tab, not all. But it can be used for reference, or ignored if it doesn't help. On that sheet, I have only filled across for the first week. The intention would be to go the entire way across, I just didn't take the time to do it at this point.

Any ideas on how to get this to work?

Leith,

The code you posted works great. I have been able to add a macro to clear the contents and combine the two into one function to update. Thank you so much for your assistance.