Summary Sheet - Total Multiple Sheets
 

I have a workbook with a time sheet for each team member by month. Each
sheet contains two columns where the team member will:
Choose a Task = Column A6:A100 (multiple tasks within one sheet)
Choose a Subtask = Column B6:B100 (multiple sub-tasks within one sheet)
Columns E6:AL100 - insert labor hrs on corresponding day from row 4.

In column AN6:AN100 each task is totaled for the month for that resource.

Now I created a summary sheet.
Column A - Every possible tasks is listed (template)
Column B - Every possible sub-task is listed (template)

I want a formula that can look at column A and column B on the same row and
if it finds that combination of task-subtask to populate the total time to
date in the corresponding column E.

I would like to break it down even further - instead of it giving me one
total to date, to have column E:DQ represent each resource by month and it
populate that months time for that resource for that task in the
corresponding column. Not sure if that is possible. I did insert a First
and Last sheet tab for the tabs to include in the totals.

My timesheet names are by first name of resource - Month.
Amy-May07, Amy-June07, John-May07, John-June07, etc ...

I have looked for hours in the discussion groups and cannot seem to find a
solution. If a macro is the answer to this, I am macro illiterate so keep
that in mind. To just lain sum it all together, I tried SUM(IF formula but
kept getting errors.
Thanks. Any help would be appreciated.

Summary Sheet - Total Multiple Sheets
 

Can you send a w/book showing exact layout of data?

toppers <at NOSPAMjohntopley.fsnet.co.uk

remove NOSPAM

"Gayla" wrote:

I have a workbook with a time sheet for each team member by month. Each
sheet contains two columns where the team member will:
Choose a Task = Column A6:A100 (multiple tasks within one sheet)
Choose a Subtask = Column B6:B100 (multiple sub-tasks within one sheet)
Columns E6:AL100 - insert labor hrs on corresponding day from row 4.

In column AN6:AN100 each task is totaled for the month for that resource.

Now I created a summary sheet.
Column A - Every possible tasks is listed (template)
Column B - Every possible sub-task is listed (template)

I want a formula that can look at column A and column B on the same row and
if it finds that combination of task-subtask to populate the total time to
date in the corresponding column E.

I would like to break it down even further - instead of it giving me one
total to date, to have column E:DQ represent each resource by month and it
populate that months time for that resource for that task in the
corresponding column. Not sure if that is possible. I did insert a First
and Last sheet tab for the tabs to include in the totals.

My timesheet names are by first name of resource - Month.
Amy-May07, Amy-June07, John-May07, John-June07, etc ...

I have looked for hours in the discussion groups and cannot seem to find a
solution. If a macro is the answer to this, I am macro illiterate so keep
that in mind. To just lain sum it all together, I tried SUM(IF formula but
kept getting errors.
Thanks. Any help would be appreciated.

Summary Sheet - Total Multiple Sheets
 

I don't if this points you in the right direction:

In your summary sheet, E6 (?):

=SUMPRODUCT(--(INDIRECT("'"& E4 &"-"& E5
&"07'!$A$6:$A$100")=$A6),--(INDIRECT("'"&E4&"-"&E5&"07'!$B$6:$B$100")=$B6),(INDIRECT("'"&E4& "-"&E5&"07'!$AN$6:$AN$100")))

E4=Resource e.g "John"
E5=Month e.g "June"



"Toppers" wrote:

Can you send a w/book showing exact layout of data?

toppers <at NOSPAMjohntopley.fsnet.co.uk

remove NOSPAM

"Gayla" wrote:

I have a workbook with a time sheet for each team member by month. Each
sheet contains two columns where the team member will:
Choose a Task = Column A6:A100 (multiple tasks within one sheet)
Choose a Subtask = Column B6:B100 (multiple sub-tasks within one sheet)
Columns E6:AL100 - insert labor hrs on corresponding day from row 4.

In column AN6:AN100 each task is totaled for the month for that resource.

Now I created a summary sheet.
Column A - Every possible tasks is listed (template)
Column B - Every possible sub-task is listed (template)

I want a formula that can look at column A and column B on the same row and
if it finds that combination of task-subtask to populate the total time to
date in the corresponding column E.

I would like to break it down even further - instead of it giving me one
total to date, to have column E:DQ represent each resource by month and it
populate that months time for that resource for that task in the
corresponding column. Not sure if that is possible. I did insert a First
and Last sheet tab for the tabs to include in the totals.

My timesheet names are by first name of resource - Month.
Amy-May07, Amy-June07, John-May07, John-June07, etc ...

I have looked for hours in the discussion groups and cannot seem to find a
solution. If a macro is the answer to this, I am macro illiterate so keep
that in mind. To just lain sum it all together, I tried SUM(IF formula but
kept getting errors.
Thanks. Any help would be appreciated.

Summary Sheet - Total Multiple Sheets
 

I copied your email exactly and I changed it to
but both ways it is still giving me an
error. The way you wrote it - it tells me it has to have @ so I changed it
and it still will not work. Help?
"Toppers" wrote:

Can you send a w/book showing exact layout of data?

toppers <at NOSPAMjohntopley.fsnet.co.uk

remove NOSPAM

"Gayla" wrote:

I have a workbook with a time sheet for each team member by month. Each
sheet contains two columns where the team member will:
Choose a Task = Column A6:A100 (multiple tasks within one sheet)
Choose a Subtask = Column B6:B100 (multiple sub-tasks within one sheet)
Columns E6:AL100 - insert labor hrs on corresponding day from row 4.

In column AN6:AN100 each task is totaled for the month for that resource.

Now I created a summary sheet.
Column A - Every possible tasks is listed (template)
Column B - Every possible sub-task is listed (template)

I want a formula that can look at column A and column B on the same row and
if it finds that combination of task-subtask to populate the total time to
date in the corresponding column E.

I would like to break it down even further - instead of it giving me one
total to date, to have column E:DQ represent each resource by month and it
populate that months time for that resource for that task in the
corresponding column. Not sure if that is possible. I did insert a First
and Last sheet tab for the tabs to include in the totals.

My timesheet names are by first name of resource - Month.
Amy-May07, Amy-June07, John-May07, John-June07, etc ...

I have looked for hours in the discussion groups and cannot seem to find a
solution. If a macro is the answer to this, I am macro illiterate so keep
that in mind. To just lain sum it all together, I tried SUM(IF formula but
kept getting errors.
Thanks. Any help would be appreciated.

Summary Sheet - Total Multiple Sheets
 

If I understand what your problem is, the solution is to start with a
leading single quote (which won't show):
'toppers@NOSPAM...

Bob Umlas

"Gayla" wrote in message
...
I copied your email exactly and I changed it to
but both ways it is still giving me
an
error. The way you wrote it - it tells me it has to have @ so I changed it
and it still will not work. Help?
"Toppers" wrote:

Can you send a w/book showing exact layout of data?

toppers <at NOSPAMjohntopley.fsnet.co.uk

remove NOSPAM

"Gayla" wrote:

I have a workbook with a time sheet for each team member by month.
Each
sheet contains two columns where the team member will:
Choose a Task = Column A6:A100 (multiple tasks within one sheet)
Choose a Subtask = Column B6:B100 (multiple sub-tasks within one sheet)
Columns E6:AL100 - insert labor hrs on corresponding day from row 4.

In column AN6:AN100 each task is totaled for the month for that
resource.

Now I created a summary sheet.
Column A - Every possible tasks is listed (template)
Column B - Every possible sub-task is listed (template)

I want a formula that can look at column A and column B on the same row
and
if it finds that combination of task-subtask to populate the total time
to
date in the corresponding column E.

I would like to break it down even further - instead of it giving me
one
total to date, to have column E:DQ represent each resource by month and
it
populate that months time for that resource for that task in the
corresponding column. Not sure if that is possible. I did insert a
First
and Last sheet tab for the tabs to include in the totals.

My timesheet names are by first name of resource - Month.
Amy-May07, Amy-June07, John-May07, John-June07, etc ...

I have looked for hours in the discussion groups and cannot seem to
find a
solution. If a macro is the answer to this, I am macro illiterate so
keep
that in mind. To just lain sum it all together, I tried SUM(IF formula
but
kept getting errors.
Thanks. Any help would be appreciated.

Summary Sheet - Total Multiple Sheets
 

I took your suggestion but still getting error - Domain Name Not Found

"Bob Umlas" wrote:

If I understand what your problem is, the solution is to start with a
leading single quote (which won't show):
'toppers@NOSPAM...

Bob Umlas

"Gayla" wrote in message
...
I copied your email exactly and I changed it to
but both ways it is still giving me
an
error. The way you wrote it - it tells me it has to have @ so I changed it
and it still will not work. Help?
"Toppers" wrote:

Can you send a w/book showing exact layout of data?

toppers <at NOSPAMjohntopley.fsnet.co.uk

remove NOSPAM

"Gayla" wrote:

I have a workbook with a time sheet for each team member by month.
Each
sheet contains two columns where the team member will:
Choose a Task = Column A6:A100 (multiple tasks within one sheet)
Choose a Subtask = Column B6:B100 (multiple sub-tasks within one sheet)
Columns E6:AL100 - insert labor hrs on corresponding day from row 4.

In column AN6:AN100 each task is totaled for the month for that
resource.

Now I created a summary sheet.
Column A - Every possible tasks is listed (template)
Column B - Every possible sub-task is listed (template)

I want a formula that can look at column A and column B on the same row
and
if it finds that combination of task-subtask to populate the total time
to
date in the corresponding column E.

I would like to break it down even further - instead of it giving me
one
total to date, to have column E:DQ represent each resource by month and
it
populate that months time for that resource for that task in the
corresponding column. Not sure if that is possible. I did insert a
First
and Last sheet tab for the tabs to include in the totals.

My timesheet names are by first name of resource - Month.
Amy-May07, Amy-June07, John-May07, John-June07, etc ...

I have looked for hours in the discussion groups and cannot seem to
find a
solution. If a macro is the answer to this, I am macro illiterate so
keep
that in mind. To just lain sum it all together, I tried SUM(IF formula
but
kept getting errors.
Thanks. Any help would be appreciated.

Summary Sheet - Total Multiple Sheets
 

Gayla

Remove the text "NOSPAM" from the address.




Gord Dibben MS Excel MVP

On Mon, 25 Jun 2007 14:08:01 -0700, Gayla
wrote:

I took your suggestion but still getting error - Domain Name Not Found

"Bob Umlas" wrote:

If I understand what your problem is, the solution is to start with a
leading single quote (which won't show):
'toppers@NOSPAM...

Bob Umlas

"Gayla" wrote in message
...
I copied your email exactly and I changed it to
but both ways it is still giving me
an
error. The way you wrote it - it tells me it has to have @ so I changed it
and it still will not work. Help?
"Toppers" wrote:

Can you send a w/book showing exact layout of data?

toppers <at NOSPAMjohntopley.fsnet.co.uk

remove NOSPAM

"Gayla" wrote:

I have a workbook with a time sheet for each team member by month.
Each
sheet contains two columns where the team member will:
Choose a Task = Column A6:A100 (multiple tasks within one sheet)
Choose a Subtask = Column B6:B100 (multiple sub-tasks within one sheet)
Columns E6:AL100 - insert labor hrs on corresponding day from row 4.

In column AN6:AN100 each task is totaled for the month for that
resource.

Now I created a summary sheet.
Column A - Every possible tasks is listed (template)
Column B - Every possible sub-task is listed (template)

I want a formula that can look at column A and column B on the same row
and
if it finds that combination of task-subtask to populate the total time
to
date in the corresponding column E.

I would like to break it down even further - instead of it giving me
one
total to date, to have column E:DQ represent each resource by month and
it
populate that months time for that resource for that task in the
corresponding column. Not sure if that is possible. I did insert a
First
and Last sheet tab for the tabs to include in the totals.

My timesheet names are by first name of resource - Month.
Amy-May07, Amy-June07, John-May07, John-June07, etc ...

I have looked for hours in the discussion groups and cannot seem to
find a
solution. If a macro is the answer to this, I am macro illiterate so
keep
that in mind. To just lain sum it all together, I tried SUM(IF formula
but
kept getting errors.
Thanks. Any help would be appreciated.