Trying to create a process either using formula or VBA to reduce the time spent searching for a specific agents name and copy a specific data highlighted in red (image).
Used index match match (unable to obtain the specific cell)
Used vlookup (unable to obtain the specific cell)
The problem i am having is the data I received depending on the attendance of each agent, the location of the data can be in different every time, also the rows and columns is not like a standard row with the header stating Agent.
Tried recording (relative or absolute) Marco to see if i can search by name, and from that start point move down 6 columns and move 6 cells to the right.
Sheets("worksheet1").Select Cells.Find(What:="Agent 16914", After:=ActiveCell, _ LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(7, 11).Range("A1:A2").Select Selection.Copy Sheets("Adherence").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False
The above code didn't work either.
Here is an example how the data is pulled as you can see the rows and column is not align to make it easy to search.
Any other method that you guys/girls can suggest?