Lookup that references closed workbooks and individual worksheets

[Darren_workforce]

The source data would appear in the format above. I have 3 different files that would be setup identically named as: Joe Smith 2023.xlsx, Sally Johnson 2023.xlsx, Jack Kirby.xlsx. Each workbook has 12 worksheets (1 for each month spelled out in their entirety. Within each worksheet there are headers for each date "dd-mmm" (Ex: B1 in image). If we can avoid VBA, all the better as I am making this for someone else to use but because of the closed workbooks, I feel that VBA would be required.

My question is what's the easiest formula that would cross reference 3 pieces of information in a brand new workbook: a. The month in A2, b. The date in A4, c. The filenames (B2:B4). Once those are located, the source data from cell B3 (above image) would populate in cells c2:c4.

Thank you for all help in advance!!

 

[Darren_workforce]

='Y:\Client Service Team\Agents\Individual Stats\[Joe Smith 2023.xlsx]February'!b3 is as far as I got regarding locating the file. It does appear to work even if the source file is closed. However, should INDIRECT OR MATCH/INDEX formulas be added to allow the user to dynamically locate the data? The data should be in row 3 but due to certain situations, could require additional data in Row 4 to be added to the data in Row 3. I'm not sure where to go from here.