You may want to look around in the VBA forum to find a solution for this problem. I've built some sheets using the built in Workbook Connections utility in the Data Tab of the toolbar but nothing as extensive as what it sounds like you're trying to do.

You might be able to play around with it, though: Data>Connections>Add. Maybe you could attach your summary sheet and one or two of the files you are attempting to reference. That way we could at least have an idea of what kind of structure you're working in.

Sorry I couldn't be of more immediate help. Maybe that gives you some ideas to start with.