Hello,

I am working with a spreadsheet that has about 50 different worksheets. Each worksheet represents a single project, and on it are the list of names of everyone in my office, and the number of hours per week (each column represents one week) they are working on the project (usually zero). There is a summary worksheet that sums up how many hours a person is working each week. The worksheets are organized so that each person is represented on the same row in each sheet. For example, John Smith's hours for project 1 for the current week are located in cell C1 for that sheet, and his hours for project 2 for the current week are located in cell C1 for that sheet, etc. I am trying to find a way to write a formula or a macro that will search through the 3d range of projects for a certain person and a certain week and tell me the individual sheets (projects) in which the number of hours the person is working is greater than zero.

I found a way to do this using a huge if statement, but the problem is the equation takes six cells to complete (excel has a formula character limit), and that I need to update the formulas every time a project is removed or added. If I am able to use a range, considering I dont change the first and last projects (worksheets), then anything falling in between them will update automatically. I have been unable to do this, however. Excel seems to have problems working with 3d ranges, and I can't figure out what to do. I would be grateful for any help! Thank you very much.

-Steve