I have one Master Financials workbook that is to sum the Profit & Loss worksheets from multiple workbooks. The master spreadsheet will lookup and sum the values from about 30 P&L spreadsheets. All of these 30 spreadsheets use the same template, with dates across row 1 and account names down column A.

My current way of lookup is:
Formula: copy to clipboard 
=VLOOKUP([Row Heading/account name], [Link to other spreadsheet array], MATCH([Column Heading/date], [Link to other spreadsheet array]),)+VLOOKUP(next spreadsheet)+VLOOKUP(next spreadsheet)+...+...+...+...+...


Here's an example for just one lookup (the actual formula is 30 times longer):
Formula: copy to clipboard 
=VLOOKUP($A9,'Macintosh HD:Users:Username:Google Drive:Finance, Legal & HR:Finance:Financial Models:Business Models:[VHA v1.0.xlsx]P&L'!$A:$BW,MATCH(B$1,'Macintosh HD:Users:Username:Google Drive:Finance, Legal & HR:Finance:Financial Models:Business Models:[VHA v1.0.xlsx]P&L'!$A$1:$BW$1,),)


My questions:
1. Is there a better formula that will sum the multiple lookups (I've been toying with SUMPRODUCT etc)?
2. Is there a better way to manage the links to the multiple workbooks, such as creating Names or a list of the workbook links, and then automatically inserting this into the formula (even if using another formula such as =INDIRECT("'"&A1&"'!$A:$BW") etc.)

Each individual spreadsheet is separately managed by General Managers and contains significant amounts of information, so placing all worksheets into just one workbook isn't an option. Finally, it's worth noting that I'm going to be copying this formula across the whole Master P&L table, meaning I'm repeating the formula 100s of times, so it becomes a bit of a nightmare to manage, especially when adding new workbook links...

Appreciate your help in advance, and I'd be happy to work with the expert that helps us to really create a substantial template for our business!

Cheers, Lincoln