scuttleButt
Programmer
Hi,
I have 8 worksheets with people entering times on various projects. Each person has 4 - 10 projects on their worksheet. On the project summary I want to search the 8 worksheets and find out who is on each project (via project ID) and total their time. So if 2 of the 8 people are on a project, I want to sum their time together. I have my VLOOKUP working to where it finds and displays the first occurance but I want it to sum all the occurances. Can anyone help? Here is my formula:
=VLOOKUP(C30,INDIRECT("'"&INDEX(associate,MATCH(TRUE,COUNTIF(INDIRECT("'"&associate&"'!A:A"),C30)>0,0))&"'!A:J"),9,0)
Associate is a named range that point to a list of the 8 worksheet names. The value that I want to lookup (project ID) is in cell C30 and I want to search A:A (project ID) in the 8 worksheets. A:J is the table and column 9 has the times that I want to sum.
Can any one help? Much appreciated!
I have 8 worksheets with people entering times on various projects. Each person has 4 - 10 projects on their worksheet. On the project summary I want to search the 8 worksheets and find out who is on each project (via project ID) and total their time. So if 2 of the 8 people are on a project, I want to sum their time together. I have my VLOOKUP working to where it finds and displays the first occurance but I want it to sum all the occurances. Can anyone help? Here is my formula:
=VLOOKUP(C30,INDIRECT("'"&INDEX(associate,MATCH(TRUE,COUNTIF(INDIRECT("'"&associate&"'!A:A"),C30)>0,0))&"'!A:J"),9,0)
Associate is a named range that point to a list of the 8 worksheet names. The value that I want to lookup (project ID) is in cell C30 and I want to search A:A (project ID) in the 8 worksheets. A:J is the table and column 9 has the times that I want to sum.
Can any one help? Much appreciated!