Macro to Look up entire workbook and return results

Hi,
firstly you need to get some consistency sorted out. Weather you use a Macro or a formula, you need to have static, consistent data to get the results you need. I'll outline the data that needs fixing.

1. Agent names. You are bound to have 2 John Does at some point in the future. You could have a John Doe1 and a John Doe2, but the accepted method around this is unique Agent ID numbers.
2. Date Names. You need to ensure the sheet names are consistent. Make them the exact same format as the way they are displayed in the summary page. This will ensure reliability in your search. Seriously consider having all the data repeated in the same sheet. You can have filters and splicers to separate the data instead of having to search through several sheets.

If you follow the rules, you don't need a macro, it can be done with a formula. I've fixed the sheet names and attached an example with formulas that update with the changes.