When I teach my Excel modeling class, a common question I get is one that is borne of complete frustration.
“I know that if I build things the right way, and from scratch, I can fit everything into one workbook, and things will flow. How do I connect a collection of disparate workbooks for different deals created by different people, and get everything to flow into a portfolio level model?”
Use the INDIRECT function.
Basically, the INDIRECT function allows one to put the names of the workbooks and where the data is in the individual asset level files into a portfolio level Excel file. Then, the INDIRECT pulls all of it together in a clean fashion. The only issue is that all of the files have to be open when you update the portfolio level file. While INDIRECT is good at pulling data, it can’t do it if the files are closed. If you want to pull data from closed files, you’ll need to use Visual Basic to do that. It’s not hard to do, but that’s a matter for another post.
See the attached example. Download all four files, open all of them, and update the links. For good measure, I also threw in an ADDRESS function to add flexibility to the INDIRECT. Play with it, and I think if you’re in portfolio management, you’ll be pleasantly surprised.