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


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=379432