Simplifying formula with 30 VLOOKUPs to externally linked workbooks

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:

Please Login or Register  to view this content.

Here's an example for just one lookup (the actual formula is 30 times longer):

Formula:

Please Login or Register  to view this content.

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

Hi Oeldere,

Any guidance on doing as such? I've never really dealt with macros all that well.

I think running a macro every time may not be the solution that the exec team is looking for, but does sound like a viable option. A formula is my preferred option.