T
thephoenix12
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 o
names of everyone in my office, and the number of hours per week (eac
column represents one week) they are working on the project (usuall
zero). There is a summary worksheet that sums up how many hours
person is working each week. The worksheets are organized so that eac
person is represented on the same row in each sheet. For example, Joh
Smith's hours for project 1 for the current week are located in cell C
for that sheet, and his hours for project 2 for the current week ar
located in cell C1 for that sheet, etc. I am trying to find a way t
write a formula or a macro that will search through the 3d range o
projects for a certain person and a certain week and tell me th
individual sheets (projects) in which the number of hours the person i
working is greater than zero.
I found a way to do this using a huge if statement, but the problem i
the equation takes six cells to complete (excel has a formula characte
limit), and that I need to update the formulas every time a project i
removed or added. If I am able to use a range, considering I don
change the first and last projects (worksheets), then anything fallin
in between them will update automatically. I have been unable to d
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.
-Stev
I am working with a spreadsheet that has about 50 different worksheets
Each worksheet represents a single project, and on it are the list o
names of everyone in my office, and the number of hours per week (eac
column represents one week) they are working on the project (usuall
zero). There is a summary worksheet that sums up how many hours
person is working each week. The worksheets are organized so that eac
person is represented on the same row in each sheet. For example, Joh
Smith's hours for project 1 for the current week are located in cell C
for that sheet, and his hours for project 2 for the current week ar
located in cell C1 for that sheet, etc. I am trying to find a way t
write a formula or a macro that will search through the 3d range o
projects for a certain person and a certain week and tell me th
individual sheets (projects) in which the number of hours the person i
working is greater than zero.
I found a way to do this using a huge if statement, but the problem i
the equation takes six cells to complete (excel has a formula characte
limit), and that I need to update the formulas every time a project i
removed or added. If I am able to use a range, considering I don
change the first and last projects (worksheets), then anything fallin
in between them will update automatically. I have been unable to d
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.
-Stev