Summary Sheet - Total Multiple Sheets

[Guest]

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

 

[Guest]

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

toppers <at> NOSPAMjohntopley.fsnet.co.uk

remove NOSPAM

 

[Guest]

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"

 

[Guest]

I copied your email exactly and I changed it to
(e-mail address removed) 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?

 

[Bob Umlas]

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

 

[Guest]

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

 

[Gord Dibben]

Gayla

Remove the text "NOSPAM" from the address.

(e-mail address removed)


Gord Dibben MS Excel MVP