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"?