I have a basic timesheet layout with the following columns: Activity, Start, End, Duration. I also have totals row at the bottom that only sums up the total duration. This little table, with its headings, is repeated 5 times on the page/sheet (vertically, for now) to give me the daily breakdown of each week.
What I am trying to do is use formulas to basically automate the process of summing up the total duration for each unique activity for each day and/or the entire week. Activities will definitely be repeated.
Now, I can do it manually in a couple of steps by myself by using advanced filter and sumproduct, but I am sharing this sheet with my coworkers, who will not be able to handle this. Therefore, I am trying to automate/"idiot proof" everything, so all they have to do is enter the activity, the start time, and the end time, and the sheet will calculate how long they did each thing and then give a summary of how much time they spent on each activity.
One of the other things I also did was to create an additional column which would identify whether or not the corresponding activity was unique or not, and then I could run an autofilter and show only unique items. This works well, except I can only do one day at a time. Also, for some reason some of the more "advanced" formulas wouldn't fill down when a row was inserted (which it will be).
I initially struggled with finding a formula for getting unique activities, but I "stole" one from another thread on this board, and it works (except for blank cells I get 0's and #N/A's, which are easily dealt with):
{=INDEX(A6:$A$18,MATCH(0,--ISNUMBER(MATCH(A6:$A$18,$H$5:H5,0)),0))}
Unfortunately, I don't know what the "--isnumber" portion is really doing here (especially the "--" prefix). I know what the function ordinarily does, but I'm a little confused as to how it's used here. If someone could explain it, that would be great.
But anyway, could any of you gurus help me out with my problem. To recapitulate, I am trying to create a function that would create a summary of the time spent on each unique activity. Since the tables/lists are dynamic, the formula (or vba) would have to adjust for any inserted rows. I've played around with dynamic named ranges and used the formula above, except it didn't go quite as planned because anytime I would insert a few rows, the formula would copy down for a couple of them, but not all, which was very annoying.
Any and all help would be appreciated. Thanks.
PS: The HTML sheetmaker wouldn't work on my computer for some reason, so I couldn't copy my screen. However, it is a very simple sheet.
What I am trying to do is use formulas to basically automate the process of summing up the total duration for each unique activity for each day and/or the entire week. Activities will definitely be repeated.
Now, I can do it manually in a couple of steps by myself by using advanced filter and sumproduct, but I am sharing this sheet with my coworkers, who will not be able to handle this. Therefore, I am trying to automate/"idiot proof" everything, so all they have to do is enter the activity, the start time, and the end time, and the sheet will calculate how long they did each thing and then give a summary of how much time they spent on each activity.
One of the other things I also did was to create an additional column which would identify whether or not the corresponding activity was unique or not, and then I could run an autofilter and show only unique items. This works well, except I can only do one day at a time. Also, for some reason some of the more "advanced" formulas wouldn't fill down when a row was inserted (which it will be).
I initially struggled with finding a formula for getting unique activities, but I "stole" one from another thread on this board, and it works (except for blank cells I get 0's and #N/A's, which are easily dealt with):
{=INDEX(A6:$A$18,MATCH(0,--ISNUMBER(MATCH(A6:$A$18,$H$5:H5,0)),0))}
Unfortunately, I don't know what the "--isnumber" portion is really doing here (especially the "--" prefix). I know what the function ordinarily does, but I'm a little confused as to how it's used here. If someone could explain it, that would be great.
But anyway, could any of you gurus help me out with my problem. To recapitulate, I am trying to create a function that would create a summary of the time spent on each unique activity. Since the tables/lists are dynamic, the formula (or vba) would have to adjust for any inserted rows. I've played around with dynamic named ranges and used the formula above, except it didn't go quite as planned because anytime I would insert a few rows, the formula would copy down for a couple of them, but not all, which was very annoying.
Any and all help would be appreciated. Thanks.
PS: The HTML sheetmaker wouldn't work on my computer for some reason, so I couldn't copy my screen. However, it is a very simple sheet.
