Function tool or programming...Help please!
 

Hi All,

I posted this question in the worksheets group but received a suggestion to
come here.

I have a workbook that has multiple sheets. Each sheet/tab has a different
project name but the cells A:13 - A:19 on each sheet all refer to team
members names. On a new worksheet w/in the workbook I want have a list of
team members names in Column A and want to add in the number of times their
name appears w/in cells A:13-A:19 across all worksheets. For Example:

Multi Sheets have the following names in the range of cells:
John
Mike
Chris

Chris shows up 5 times total throughout all the sheets so on the New Sheet
"Resources" I want it to look like this:

Column A Column B
John 0
Mike 0
Chris 5 - this is a sum of the number of times "Chris" was
listed in the cells across all the worksheets.

Note: Someone suggested signing a name to a cell but I have 45 resource
names.I
don't want to have 45 blank cells on each sheet for each of the resource to
match to.

Help!! It may be simple, but I am stuck in the mud....
Thanks in advance!

Function tool or programming...Help please!
 

Here is one way.

On your summary sheet, build a list of all of the sheet names.

In B2:B7, add the people's names.

Then in C2, add this formula

=SUMPRODUCT(--(COUNTIF(INDIRECT("'"&$I$1:$I$3&"'!A$13:A$19"),"bo b")))

and copy down.

Note that the range $I$1:$I$4 must cover the entire range of sheets other
than the master, no blanks.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sukismomma" wrote in message
...
Hi All,

I posted this question in the worksheets group but received a suggestion
to
come here.

I have a workbook that has multiple sheets. Each sheet/tab has a different
project name but the cells A:13 - A:19 on each sheet all refer to team
members names. On a new worksheet w/in the workbook I want have a list of
team members names in Column A and want to add in the number of times
their
name appears w/in cells A:13-A:19 across all worksheets. For Example:

Multi Sheets have the following names in the range of cells:
John
Mike
Chris

Chris shows up 5 times total throughout all the sheets so on the New Sheet
"Resources" I want it to look like this:

Column A Column B
John 0
Mike 0
Chris 5 - this is a sum of the number of times "Chris"
was
listed in the cells across all the worksheets.

Note: Someone suggested signing a name to a cell but I have 45 resource
names.I
don't want to have 45 blank cells on each sheet for each of the resource
to
match to.

Help!! It may be simple, but I am stuck in the mud....
Thanks in advance!

Function tool or programming...Help please!
 

Hi Bob!

Sorry but I am still a little lost. My project names are extremely different
from one another. I added the list of them to the summary sheet, have the
names listed but curious as to how I will reflect the sheet names in the
range. Here are some sample titles of my sheets...

COMET_ETL
Eureka Decommission
Yield Management
Emilia - APIS

Here is another thought on how to resolve this but I can't figure this one
out either. Not sure my brain is all the way on this morning...

Can I do a find on a name and count the number of times it shows up across
all sheets and have that number posted on my summary sheet? I think it is
doing the same thing but in a roundabout way....




"Bob Phillips" wrote:

Here is one way.

On your summary sheet, build a list of all of the sheet names.

In B2:B7, add the people's names.

Then in C2, add this formula

=SUMPRODUCT(--(COUNTIF(INDIRECT("'"&$I$1:$I$3&"'!A$13:A$19"),"bo b")))

and copy down.

Note that the range $I$1:$I$4 must cover the entire range of sheets other
than the master, no blanks.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sukismomma" wrote in message
...
Hi All,

I posted this question in the worksheets group but received a suggestion
to
come here.

I have a workbook that has multiple sheets. Each sheet/tab has a different
project name but the cells A:13 - A:19 on each sheet all refer to team
members names. On a new worksheet w/in the workbook I want have a list of
team members names in Column A and want to add in the number of times
their
name appears w/in cells A:13-A:19 across all worksheets. For Example:

Multi Sheets have the following names in the range of cells:
John
Mike
Chris

Chris shows up 5 times total throughout all the sheets so on the New Sheet
"Resources" I want it to look like this:

Column A Column B
John 0
Mike 0
Chris 5 - this is a sum of the number of times "Chris"
was
listed in the cells across all the worksheets.

Note: Someone suggested signing a name to a cell but I have 45 resource
names.I
don't want to have 45 blank cells on each sheet for each of the resource
to
match to.

Help!! It may be simple, but I am stuck in the mud....
Thanks in advance!

Function tool or programming...Help please!
 

"Sukismomma" wrote in message
...
Hi Bob!

Sorry but I am still a little lost. My project names are extremely
different
from one another.

I had assumed that, which is why I suggested this solution as against
another.

I added the list of them to the summary sheet, have the
names listed but curious as to how I will reflect the sheet names in the
range. Here are some sample titles of my sheets...

COMET_ETL
Eureka Decommission
Yield Management
Emilia - APIS

This list of names would go into the cells I1:In in my formula. Simply add
them one by one.

Here is another thought on how to resolve this but I can't figure this one
out either. Not sure my brain is all the way on this morning...

Can I do a find on a name and count the number of times it shows up across
all sheets and have that number posted on my summary sheet? I think it is
doing the same thing but in a roundabout way....

Let's stick with this, the net result will be the same, even assuming we
could get the find to work.

Bob

Function tool or programming...Help please!
 

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.

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....

Thanks for your patience in advance!
"Bob Phillips" wrote:

"Sukismomma" wrote in message
...
Hi Bob!

Sorry but I am still a little lost. My project names are extremely
different
from one another.

I had assumed that, which is why I suggested this solution as against
another.

I added the list of them to the summary sheet, have the
names listed but curious as to how I will reflect the sheet names in the
range. Here are some sample titles of my sheets...

COMET_ETL
Eureka Decommission
Yield Management
Emilia - APIS

This list of names would go into the cells I1:In in my formula. Simply add
them one by one.

Here is another thought on how to resolve this but I can't figure this one
out either. Not sure my brain is all the way on this morning...

Can I do a find on a name and count the number of times it shows up across
all sheets and have that number posted on my summary sheet? I think it is
doing the same thing but in a roundabout way....

Let's stick with this, the net result will be the same, even assuming we
could get the find to work.

Bob

Function tool or programming...Help please!
 

"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.