Search and Return References from Multiple Workbooks

ExcelJournyman

New Member
Joined
Jun 19, 2018
Messages
5
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.


AB
1Registrations:
2Registrant:Invoice
3
4Baseball
5Marcus50
6Dan50
7Olivia50
8Ben50
9
10Football
11Kyle60
12Matt60
13Ian60
14Jeff60
15
16Payments
17RegistrantPaid
18
19Baseball
20Marcus50
21Dan40
22Olivia50
23Ben50
24
25Football
26Kyle60
27Matt30
28Ian60
29Jeff60

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi exceljournyman,

Questions

Would you be happy with a VBA solution?

Are all the monthly workbooks in the same folder?

Regards
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top