Creating link to closed workbooks with variable names

I need to copy cells from a number of closed workbooks into a separate workbook. I was hoping to create an Excel formula to do that. On each row of the target worksheet, all the information comes from the same cells (E27, F27, H27, H46, E13, F13 & H13) on the source workbooks -- but each row needs to access a different workbook. The workbooks are all similarly named with the exception of a code prefix. The code prefix can be found in column B of each row on the target worksheet. For example, all source file names are something like "xxx - Source Data" where xxx is in each populated row in column B. Is there a way I can use a cell value in the path/file name to retrieve the data from a closed workbook? Or do I have to create a macro to do this. I believe the Indirect function will only work when all workbooks are open.

Thanks for the info but I don't think this will work for me. I can't download it to my laptop due to corporate firewalls. I have a Mac at home with some software that makes it behave like a Windows environment. Downloaded it there hoping I could see the VBA code to replicate it but it is password protected. Any ideas on what else to try??

There are about 80 source workbooks and, for each row on the target workbook, I'd be pulling 7 cells from one of the workbooks. Each row on the target workbook pulls the cells from a different source workbook. I was trying to avoid having to enter the formula into each of the more than 500 cells. Since the only thing that changes in the source workbook name for each row on the target workbook/sheet is the code in column B of that row, I was hoping there would be a way to include the cell value in the file name. That way I would be able to enter the formulas for one row on the target worksheet and copy it down to the rest of the rows.

Is there a way I can replace file name [7608 - 2012 Source.xls], etc., with [CellBValue - 2012 Source.xls] and have the data pulled from a closed workbook? If I can't use a formula, is there macro code that can pull the value for a cell from a closed workbook with a variable name for the file? I could then expand the code for the remaining cells.

A 7608 ='M:\Path\[7608 - 2012 Source.xls]Data Diff'!$E$27
A 7609 ='M:\Path\[7609 - 2012 Source.xls]Data Diff'!$E$27
A 7615 ='M:\Path\[7615 - 2012 Source.xls]Data Diff'!$E$27
A 7632 ='M:\Path\[7632 - 2012 Source.xls]Data Diff'!$E$27