"Sukismomma" wrote in message
...
I don't want to be a pain, but I am still confused.

I have the list of projects created on my resource sheet, I have all the
resource names in column B - but there are alot more than what you
indicated,
they go from B2:B46.

So just replace the references that I gave you , I1:I3 to B2:B46

Since I have 33 separate sheets, that number will increase and descrease
as
projects are added/closed, is there a way to reference the range of sheets
rather than the individual names? Maybe I am way in over my head here....

I did try and make it dynamic before I posted it but failed. But I seem to
have sorted that now, so try this instead

Put the names in A2:An on the master sheet. We know the sheet names are in
B2:B46. In C2, add this version of the formula

=SUMPRODUCT(--(COUNTIF(INDIRECT("'"&OFFSET($B$2,,,COUNTA($B:$B)) &"'!A$13:A$1
9"),A2)))

and copy down. If you have headings in row 1, specifically in B1, change the
formula slightly to

=SUMPRODUCT(--(COUNTIF(INDIRECT("'"&OFFSET($B$2,,,COUNTA($B:$B)-1)&"'!A$13:A
$19"),A2)))

That should work as it is, and allow for changing the projects.

BTW, if you are feeling brave, and the projects could change name, you could
use this technique to protect you against continually updating the list.
Instead of putting the name in B2:B46, use this formula

=MID(CELL("filename",'sheet_name'!A1),FIND("]",CELL("filename",'sheet_name'!
A1))+1,99)

You would need to change it every row for the correct sheet name, but if you
then change the project, and that sheet name, the list will change with it.