Dynamically pull rows from multiple workbooks to combine on one workbook

Hey all, I'm a bit stumped on what seems to me a simple thing to do.

Essentially, I have multiple workbooks that were created off of a template. As I enter a global name applied to each workbook, I add something at the end of the name to make each workbook unique. I need to be able to pull 4 sequential rows from a sheet, Lead, off of each workbook and compile them into a master list so to speak. Additionally, I'm trying to do this dynamically so when another workbook is added, it will add it to the compilation. Finally, each sequence of rows has a total amount on the last row; I'd like a way to be able to create a grand total from the totals of each workbook that was entered.

I have two sheets on the master workbook, Sheet1 and Lead. So far, I've managed to create a basic table on Sheet1 for testing with the following headers: Project Name; and, Project Number. Then I use TEXTAFTER(TEXTBEFORE(CELL("filename"),".xlsx]"),"[") to get the base Project Name, and have the user manually enter the number of related workbooks. So for example, I have 2 workbooks named "Book1 Test1" and "Book1 Test2"; the master workbook I'd be working in would just be called "Book1" and then the user would have to manually enter "Test1" and "Test2" in the "Project Number" header.

Afterwards, I have to dynamically map each Project Number to the Project Name which I'm having a little trouble with. What I've got right now is just =A2 & " " & B(2/3/4/...) & ".xlsx" which is a bit lacking. Lastly, I have =HSTACK(IF('[Book1 Test1.xlsm]Lead'!A22:A27="", "", '[Book1 Test1.xlsm]Lead'!A22:A27), '[Book1 Test1.xlsm]Lead'!D22:D27) which pulls the two relevant ranges that I'm trying to compile. I have to dynamically map these ones as well.

The end result compiles the two ranges, A22:A27 and D22:D27 from every workbook's 'Lead' sheet into the master workbook.

Some images for reference:

https://ibb.co/jky5rH52

https://ibb.co/T5ddSck