Agent Report Parser

[Anthony_L]

Hello,

I'm trying to put together an Agent Report Parser, and I'm having some trouble. I want to be able to type the Agent's Name in the cell and also the "Week Of" in another cell, and then making it auto-populate the data for the fields provided. The data is on different tabs, so I'm getting I need a index-match, indirect function.

Here is a screenshot of an example: https://imgur.com/a/XB3cdCF

<colgroup><col style="mso-width-source:userset;mso-width-alt:5083;width:104pt" width="139"> <col style="mso-width-source:userset;mso-width-alt:5632;width:116pt" width="154"> </colgroup><tbody>
[TD="class: xl65, align: left"][/TD]

[TD="class: xl65, align: left"][/TD]

</tbody>

<tbody>
[TD="class: xl65, align: left"][/TD]

[TD="class: xl65, align: left"][/TD]

[TD="class: xl65, align: left"][/TD]

[TD="class: xl65, align: left"][/TD]

</tbody>

 

[mumps]

From which sheet do you want to retrieve the data for the fields provided? Could you post a link to your file instead of a screenshot? It's difficult to work with a screenshot.

 

[Anthony_L]

https://drive.google.com/file/d/1S-a5RSt3ncc7WH_s2GHPJrulsvO8yQtb/view?usp=sharing

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the "Report Parser" tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a name in cell B2 and then enter a sheet name in cell B3 and exit the cell. Make sure you enter the sheet name after you have entered the name.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim name As Range
    Set name = Sheets(Target.Value).Range("AA:AA").Find(Range("B2").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not name Is Nothing Then
        Sheets(Target.Value).Range("AC" & name.Row & ":AH" & name.Row).Copy
        Range("B5").PasteSpecial xlPasteValues, Transpose:=True
    End If
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[Anthony_L]

Wow! It works. You went above and beyond the formulas/functions altogether. haha. nicely done.

 

[Anthony_L]

Will this continue to work if I had more names and more sheets in the future?

 

[Anthony_L]

And do you think this is efficient in typing the name and the week or is using a drop down menu for each better?

 

[mumps]

It will work with any number of sheets. To avoid typing errors, I would suggest using a drop down list in B2 and B3. You could add a new sheet, list the names in column A, the sheet names in column B and then use a formula in Data Validation that refers to the new sheet and ranges when creating the drop down lists.