I am trying to create a dynamic dashboard where the underlying info is in other files and the files that are being compared will change over time with the same underlying layout. (cell references will be the same in each of the underlying files and sheets)

I want to be able to put the names of whatever files and sheets in some cells in Excel and have formulas in other cells go reference the cell with the file names in it to get the info in it.

For example, cell A1 would have in it H:\test1.xls, cell B1 would have Sheet1
Cell A2 would have h:\test2.xls cell B2 would have Sheet1

In cell H1, I want to put a formula in that does the equivalent of 'H:\[test1.xls]Sheet1'!$D$1 where the path and sheet name change by what I put in A1.

I would appreciate any suggestions.

Thanks,
Rich