PaulAsaran
New Member
- Joined
- Feb 11, 2019
- Messages
- 22
I've built a timesheet with 15-minute increments. Users enter their task and what project the task is part of, and a single project can have multiple tasks. All projects are in one column and all tasks are in a neighboring column. Total time is listed beside them. The range is D2:G166 (NOTE: Column F is a notes column and has no value for the purposes of the summary table). The sheet is named "Detail". See example below:
<tbody>
</tbody>I've been using the timesheet for nearly a year now and it works fine, but I've decided it's time to create a summary page for it on a different sheet. My first step was to create a list of unique tasks using an array formula:
This formula was then drag-copied down over the range A2:A167 and works fine.
Next, I created an Hours column that determines how much time is paid to each individual task, regardless of where it appears in the original table:
The formula uses the result of the previous formula as the criteria for the SUMIF to locate the appropriate numbers to sum up. This too works just fine across the range C2:C167.
Now we get to the problem. In column B, I created a formula that would determine what project each task was assigned to:
Like the previous formula for summing hours, this one is referencing the result of the first formula as the VLOOKUP lookup value. But instead of doing that, it targets seemingly random tasks. I can't figure out why it would do that, but I've determined that all values are correct as I understand VLOOKUP—and I should understand it, as I use it frequently. Is it because the first formula is an array formula? If that's a problem, why does the SUMIF formula work fine when the VLOOKUP doesn't? I am thoroughly confused and can't seem to locate any information on this particular problem in my online searches, so here we are.
The final summary table is meant to look like the below (using the example table above as a reference):
<tbody>
</tbody>
| D | E | G |
| Task | Project | Hours |
| Task 1 | Project 1 | |
| Task 1 | Project 1 | .5 |
| Task 2 | Project 2 | .25 |
| Task 3 | Project 1 | .25 |
| Task 2 | Project 2 | .25 |
| Task 4 | Project 3 | |
| Task 4 | Project 3 | |
| Task 4 | Project 3 | .75 |
<tbody>
</tbody>
Code:
{=IFERROR(INDEX(Detail!$D$2:$D$166,MATCH(0,COUNTIF($A$1:A1,Detail!$D$2:$D$166),0)),"")}Next, I created an Hours column that determines how much time is paid to each individual task, regardless of where it appears in the original table:
Code:
=IF(OR(A2="",A2=0),"",SUMIF(Detail!$D$2:$D$166,A2,Detail!$G$2:$G$166))Now we get to the problem. In column B, I created a formula that would determine what project each task was assigned to:
Code:
=IFERROR(VLOOKUP(A2,Detail!$D$2:$E$166,2),"")The final summary table is meant to look like the below (using the example table above as a reference):
| A | B | C |
| Task | Project | Hours |
| Task 1 | Project 1 | .5 |
| Task 2 | Project 2 | .5 |
| Task 3 | Project 1 | .25 |
| Task 4 | Project 3 | .75 |
<tbody>
</tbody>
