So I'm trying to make life easy with a formula I've been working on.
My formula is =SUMIFS(H2:H27282,G2:G27282,"1",F2:F27282, "*"&OFFSET(L2,0,-1)&"*")
Essentially in the H Column I have the Minutes of a task which I'm trying to total up. in the G Column I have the "Task Codes" 1,2,3,4,5,6,7,8, which all represent a specific task like walking, running, jumping jacks, ect. Then in the F column I have names of the person doing those tasks.The point of this excel document is to track the number of minutes these people are doing various tasks over the course of a year. As you may expect, there will be many entires under the same people and the same task codes. By the end of the year, I'll have thousands of entry logs to report on. It's for this reason I created the K column to list the names of every member (Only once) included on this list. The K Column is my reference list and I use the L Column with the OFFSET function to directly report the total minutes that person in Column K is doing the identified "Task Code". In this formula I showed, the Code I'm reporting on is "1".
THE DILEMMA: To report the total minutes in column L. I don't want to have to go into the formula each time and change the OFFSET reference to the cell bellow it. If I have 300 people participating in this tracking report, It's going to be an hour of chaning L2 to L3, and over and over and over again until I reach L300.
WHAT I'M WONDERING: I'm curious if I can use the Copy drag function to my advantage or do something similar. When I copy and drag, all the cell references +1.
So in effect, if in row 2 the correct formula is =SUMIFS(H2:H27282,G2:G27282,"1",F2:F27282, "*"&OFFSET(L2,0,-1)&"*") then when I copy drag down I get:
=SUMIFS(H3:H27283,G3:G27283,"1",F3:F27283, "*"&OFFSET(L3,0,-1)&"*")
So I like the L reference moves to 3. However I want the H, G, and F reference to stay 2.
THE QUESTION: Is there a way to copy and paste this formula into all the rows of L, while maintaining the references for H,G, and F, but allowing L to continualy increase per cell it's draged into?
Bookmarks