Search and Return References from Multiple Workbooks

[ExcelJournyman]

I have multiple workbooks for each month (Named January, February, March, etc) with sheets within the workbooks for each day (010119 for January 1st 2019, 010219 for January 2nd 2019 for example).

In each sheet is a list of people who have registered and paid for a sport (see below). Some people appear multiple times for the same sport across multiple workbooks and/or sheets.

Basically, I'm looking for a way to search across multiple workbooks and worksheets to find every instance where someone PAID for a sport registration (Baseball for example). Ideally, I'd like my parameter to be: the sport (Like Baseball). I'm aware I may need a variable sheet reference (which I am somewhat familiar with using =Indirect) and the variable workbook reference (somewhat familiar with) as references as well.

What I would like returned is: The Names of registrants and the total amount they have paid. While I'd ideally like a return value for each instance they paid, this is not absolutely necessary. Also, you will notice that the is a Registration section and a Payments section. I need to ignore any information in the registration section. Only the payment section is important. I really wish this was formatted differently, but its what I have to work with.



Example: In workbook January in sheet 010119, Dan Registers for baseball for $50. This appears in the registration section. On that day Dan pays $40. This appears in the payments section. In workbook January in sheet 012319, Dan pays the remaining $10. This does not appear in the registration section but will appear in the payments section.

What I ideally want returned is:

Dan: $40
Dan: $10

but this is also fine:

Dan:$50

Alternatively, I'd also be happy with a data dump that returns all a row which contains sport, registrant, and amount paid for every payment entry. At least that is something I can easily use a pivot table on.

Please let me know if you need clarification on what I am trying to do, thanks.


[TABLE="width: 231"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="colspan: 2"]Registrations:[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Registrant:[/TD]
[TD]Invoice[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="colspan: 2"]Baseball[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Marcus[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Dan[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Olivia[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Ben[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="colspan: 2"]Football[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Kyle[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Matt[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Ian[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Jeff[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD="colspan: 2"]Payments[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Registrant[/TD]
[TD]Paid[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD="colspan: 2"]Baseball[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Marcus[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Dan[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Olivia[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]Ben[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD="colspan: 2"]Football[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]Kyle[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]Matt[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]Ian[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]Jeff[/TD]
[TD="align: right"]60[/TD]
[/TR]
</tbody>[/TABLE]

 

[Brombrough]

Hi exceljournyman,

Questions

Would you be happy with a VBA solution?

Are all the monthly workbooks in the same folder?

Regards

 

[ExcelJournyman]

Hello Brombrough,

Thank you for your reply. I do not have experience with VBA but I am open to the idea. An issue I foresee is that I might not be able to make adjustments if I need to change some details. All of the workbooks are not in the same folder but I can easily copy all of the necessary workbooks into a single folder.