Hi there guys! Hopefully this question isn’t super complicated, but I am trying to make things simple for my co-workers. We’re making a project hours tracker using excel since our team is too small to need an online project management service. Here’s the gist of what I want to do…

In one workbook, each employee has identical worksheet templates which can be filled in as needed with tasks done during the month. Let’s say we have 3 employees on the team. That means, our workbook contains 3 separate worksheets which act as individual logs for each employee. The name of each worksheet is the name of the employee (‘Employee Name’). In their respective worksheets, employees are to log a work entry by filling in three columns per entry: the project name (drop-down selection), a description of the task, and then the numbers of hours spent on that task.

Also in the workbook, there is a fourth worksheet which is for our supervisor. I would like for him, from a drop down, to select the employee’s name he wishes to make an hours report on. So, when he selects the name…maybe we can say John Smith is the employee…the worksheet essentially fills out a report with the projects John Smith worked on and the total hours for each, then creates a pie chart showing percentages.

Most of the functionality of this idea is working pretty well, but for the tallying of hours in the final report, I’m having trouble getting functions to reference the appropriate worksheet. To tally hours, I’m using a SUMIF formula that looks like this:

=SUMIF(‘Employee Name’!A:A,’Hours Report’!A3,’Employee Name’!D:D)

‘Employee Name’!A:A references the full column of project names in the employee log. ‘Hours Report’!A3 references the cell with the project name being tallied in the final report. ‘Employee Name’!D:D references the column of hours logged by the employee.

The worksheet reference name (‘Employee Name’) needs to be changed based on what employee the final report is about, so it can address the appropriate log to sum hours from. I haven’t a clue how to do thus, and tried things like ADDRESS, VLOOKUP, and others without much luck. Any suggestions, thoughts, or solutions?

​Hi there guys! Hopefully this question isn’t super complicated, but I am trying to make things simple for my co-workers. We’re making a project hours tracker using excel since our team is too small to need an online project management service. Here’s the gist of what I want to do…In one workbook, each employee has identical worksheet templates which can be filled in as needed with tasks done during the month. Let’s say we have 3 employees on the team. That means, our workbook contains 3 separate worksheets which act as individual logs for each employee. The name of each worksheet is the name of the employee (‘Employee Name’). In their respective worksheets, employees are to log a work entry by filling in three columns per entry: the project name (drop-down selection), a description of the task, and then the numbers of hours spent on that task. Also in the workbook, there is a fourth worksheet which is for our supervisor. I would like for him, from a drop down, to select the employee’s name he wishes to make an hours report on. So, when he selects the name…maybe we can say John Smith is the employee…the worksheet essentially fills out a report with the projects John Smith worked on and the total hours for each, then creates a pie chart showing percentages.Most of the functionality of this idea is working pretty well, but for the tallying of hours in the final report, I’m having trouble getting functions to reference the appropriate worksheet. To tally hours, I’m using a SUMIF formula that looks like this:=SUMIF(‘Employee Name’!A:A,’Hours Report’!A3,’Employee Name’!D:D) ‘Employee Name’!A:A references the full column of project names in the employee log. ‘Hours Report’!A3 references the cell with the project name being tallied in the final report. ‘Employee Name’!D:D references the column of hours logged by the employee. The worksheet reference name (‘Employee Name’) needs to be changed based on what employee the final report is about, so it can address the appropriate log to sum hours from. I haven’t a clue how to do thus, and tried things like ADDRESS, VLOOKUP, and others without much luck. Any suggestions, thoughts, or solutions?  Read More

​