I currently have lots of different excel workbooks in different folders on my computer, similar to financial workbooks for each month, and they're all in the same template. I'd like to make a "master" workbook which collates the info from each of these workbooks in a separate folder, for one large summary for each month together.
Now linking the different workbooks and a specific worksheet "Estimate" in them I can do fairly easily e.g. cell A1 might eventually look something like:
='C:\Finance\January\[Finance_Jan.xlsm]Estimate'!B10+'C:\Finance\February\[Finance_Feb.xlsm]Estimate'!B10+'C:\Finance\March\[Finance_March.xlsm]Estimate'!B10
The issue is, is that the formulae eventually get really long and if I want to add things or modify things it's quite difficult. Ideally, in the master workbook, I'd like to have a list of the Workbook names and their locations in a table. Then I could refer to them using their cells, so instead I could hopefully do something like, for example:
=D1!B10+D2!B10+D3!B10
with each location stored in D1, D2, etc.
However, trying something like this doesn't work.
Any suggestions?
Thanks.
