Select and sum

[joseway1979]

Hi,

I'm trying to create an hours sheet, where I have employees select a dropdown with all the projects they are working on and then writing down the hours for it for each day of the month, e.g. the sheet will have days 1 to 31, the next column will be dropdown and the 3rd column would be where they enter the hours.

Now, I want to create a summary sheet whereby I have all the months at the top and all the projects on the side. What I would like to do is create a formula whereby in January, Project X will be filled with the sum of what the employee has put down in his sheet, so if he has worked the 2nd, 5th and 8th Jan @ 8 hours each day, then the sheet in Jan for that project will = 24.

Any help would be much appreciated.

 

[sophie619]

Will each day only have one row? That is, can the employees only work on one project per day?

Does/will this worksheet reside on a server where all the employees have access to it? Or will they keep their own local copy of it?

 

[joseway1979]

Hi,

It actually will have 2 columns, now I think about it. If it's a full day for that project then they will enter 8, if they worked on 2 then it will be 4 and then 4 on another column, which I guess will mean I will have to have 4 columns in total, if you see what I mean, for hours worked on projects.

No, they have their own sheets, but, I would save them to a timesheet forlder, so I could update them.

Hope that's answered your question.

Will each day only have one row? That is, can the employees only work on one project per day?

Does/will this worksheet reside on a server where all the employees have access to it? Or will they keep their own local copy of it?

 

[sophie619]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]DAY[/TD]
[TD]Project[/TD]
[TD]Hrs[/TD]
[TD]Project[/TD]
[TD]Hrs[/TD]
[TD]Project[/TD]
[TD]Hrs[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
For argument's sake, let's say your truncated hours sheet looks like this. I have a few more questions
1. Will the hours have any validation to them (i.e., will they need to add to 8 every day? What happens if they say they worked on 3 projects for 8 hours each,)?
2. Will each month will be in a separate tab of the workbook? Or would you just add columns to the one sheet so they can continue the next month on that same one?
3. Have you created the above worksheets for your employees, or are you still in the planning stages of this?

 

[joseway1979]

That's pretty much what it will look like.
1) They will either be 8 hours or 4. No more than 2 projects a day
2) Yes, unless you think it would be easier to just have it on one sheet.
3)Below is pretty much what they have, but it can be changed, if you think there is a better way.

Thanks,

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]DAY[/TD]
[TD]Project[/TD]
[TD]Hrs[/TD]
[TD]Project[/TD]
[TD]Hrs[/TD]
[TD]Project[/TD]
[TD]Hrs[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
For argument's sake, let's say your truncated hours sheet looks like this. I have a few more questions
1. Will the hours have any validation to them (i.e., will they need to add to 8 every day? What happens if they say they worked on 3 projects for 8 hours each,)?
2. Will each month will be in a separate tab of the workbook? Or would you just add columns to the one sheet so they can continue the next month on that same one?
3. Have you created the above worksheets for your employees, or are you still in the planning stages of this?

 

[sophie619]

ok so then you have columns A-E on the sheet for each month. Let's assume the sheets are named "jan" "feb" "mar"...etc.

Your summary page will look like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Projects[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[/TR]
[TR]
[TD]A345[/TD]
[TD]=SUMIF(jan!$B$2:$B$32,A2,jan!$C$2:$C$32)+SUMIF(jan!$D$2:$D$32,A2,jan!$E$2:$E$32)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]M321[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]V78[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Try that and see if it works. It worked on my mock-up spreadsheet

 

[joseway1979]

That has worked a treat. Thank you so much for that.

ok so then you have columns A-E on the sheet for each month. Let's assume the sheets are named "jan" "feb" "mar"...etc.

Your summary page will look like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Projects[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[/TR]
[TR]
[TD]A345[/TD]
[TD]=SUMIF(jan!$B$2:$B$32,A2,jan!$C$2:$C$32)+SUMIF(jan!$D$2:$D$32,A2,jan!$E$2:$E$32)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]M321[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]V78[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Try that and see if it works. It worked on my mock-up spreadsheet