I am completely stuck with 3 variables

[emsa]

I am setting up a time sheet for an employee and I have a table with 3 variables and I am trying to add things up in master sheet, however I can not figure out how to do so.

The three variables are the: Project Name, Work Performed, and Number of hours spent.

Say I have 6 Columns.
In Row 1, cells B&C are merged with the Project Name. Similarly cells D&E, and F&G.

Column A has all the days in the year.

Under each project name, columns B,D, & F there is an option of Work Performed from a pull down, and in the cell next to it (Columns C,E,&G) is where I input the numbers of hours spent.

On the master sheet I am trying to track all the hours spent per work performed per job. Here I have the Job Names set up across the X axis and the different possible Work Performed set up in the Y axis. Inside the tables I want it to sum up the values from my other sheet. Any Solutions?

 

[texasalynn]

sumproduct will do what you need. I won't be able to put it together today - but if tomorrow you don't have an answer I will give you an example.

 

[emsa]

to clarify

just to clarify,

On the master sheet collecting all the data. The project names and work performed are locked in place, the only thing that will change is the number of hours spent doing each task. This number will be retrieved from the other sheet and will add the hours for a certain task for a certain project on all days.

However on these other sheets, the project name and work performed aren't always in the same location from day to day.

And thank you for the quick response yesterday, but I still do not have an answer and would appreciate it greatly if someone can help.

 

[arthurbr]

Hi,
first of all I would advise to dispense with merged cells. They are nothing but trouble when trying to extract data ( even if it looks nice).
Could you possible post a sample of your data and what you are trying to do?

 

[emsa]

Here is a small portion of my timesheet: the project name may change week to week and are selected from a pulldown, and the tasks can be different everyday and are also selected from a pulldown.

Please ignore the "...." I just used it as a spacer b/c I am not familiar w/ HTML.

.................project name.............project name...............project name.....Hrs Worked that day
8/6/07.......Task......#hrs.............Task......#hrs..............Task......#hrs.....(sum all hrs to left)
8/7/07.......Task......#hrs.............Task......#hrs..............Task......#hrs.....(sum all hrs to left)
8/8/07.......Task......#hrs.............Task......#hrs..............Task......#hrs.....(sum all hrs to left)
8/9/07.......Task......#hrs.............Task......#hrs..............Task......#hrs.....(sum all hrs to left)
8/10/07.....Task......#hrs.............Task......#hrs..............Task......#hrs.....(sum all hrs to left)
8/11/07.....Task......#hrs.............Task......#hrs..............Task......#hrs.....(sum all hrs to left)
.................project name.............project name...............project name
8/12/07.....Task......#hrs.............Task......#hrs..............Task......#hrs.....(sum all hrs to left)
8/13/07.....Task......#hrs.............Task......#hrs..............Task......#hrs.....(sum all hrs to left)
8/14/07.....Task......#hrs.............Task......#hrs..............Task......#hrs.....(sum all hrs to left)
8/15/07.....Task......#hrs.............Task......#hrs..............Task......#hrs.....(sum all hrs to left)
8/16/07.....Task......#hrs.............Task......#hrs..............Task......#hrs.....(sum all hrs to left)
8/17/07.....Task......#hrs.............Task......#hrs..............Task......#hrs.....(sum all hrs to left)

My master sheet looks like this, all the possible projects in the X axis, and all the possible tasts in the Y. The bottom row I am trying to track the total amount of time spent at each project:

................project A..........project B..........project C..........project D
Task 1:........(hrs)................(hrs)................(hrs)................(hrs)
Task 2:........(hrs)................(hrs)................(hrs)................(hrs)
Task 3:........(hrs)................(hrs)................(hrs)................(hrs)
Task 4:........(hrs)................(hrs)................(hrs)................(hrs)
Task 5:........(hrs)................(hrs)................(hrs)................(hrs)
Task 6:........(hrs)................(hrs)................(hrs)................(hrs)
TOTAL:..(sum above).....(sum above).....(sum above).....(sum above)


I hope that makes sense! Thanks in advance!

 

[texasalynn]

thanks for the sample, but it doesn't have any actual numbers nor what you expect the results to be. That would be a tremendous help

 

[emsa]

sorry, here you go. Hope this makes sense, I tried to make it as clear as possible:

...................CVS STORE................174 Bridge St.............Dialysis Cntr......Hrs Worked that day
8/6/07.......Drywall......5................Framing......3...........Task......#hrs..................8..........
8/7/07.......Insulate.....2................Task......#hrs...........Window.....5....................7........
8/8/07.......Task......#hrs................Drywall ...8.............Task......#hrs..................8...........
8/9/07.......Doors........4................Framing....1.............Accesso.....3....................8..........
8/10/07....Accesso......2.................Doors......4............Insulate......3....................9..........
8/11/07....Insulate......4................Insulate....3..............Task.....#hrs...................7.........
.................174 Bridge St...............Walgreens...................Costco...........
8/12/07.......Drywall......5................Framing......3...........Task......#hrs..................8..........
8/13/07....Accesso......2.................Doors......4............Insulate......3....................9..........
8/14/07.......Doors........4................Framing....1.............Accesso.....3....................8..........
8/15/07.......Task......#hrs................Drywall ...8.............Task......#hrs..................8...........
8/16/07.......Drywall......5................Framing......3...........Task......#hrs..................8..........
8/17/07....Insulate......4................Insulate....3..............Task.....#hrs...................7........



Master Sheet:

................CVS STORE.........Dialysis Cntr.........Walgreens..........Costco .........174 Bridge St.
Drywall:..........5........................0........................8........................0.......................18..
Doors..:..........4........................0........................4........................0.......................8..
Accesso:.........2........................3........................0........................3.......................2..
Window:.........0........................5........................0........................0.......................0..
Insulate:.........4........................3........................3........................3.......................7..
Framing:.........0........................0........................7........................0.......................4..
TOTAL:..........15......................11.......................22.......................6......................39......