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.

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.

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.

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.

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.

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.

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.