Use a cell value in a address link

I wasn't actually sure what to title this issue. What I have is a summary worksheet with two columns. One column is for Project ID number and the other is an address link to a specific cell in the worksheet corresponding to the Project ID. Perhaps the best way to explain this is by giving an example:

Project ID (Column 1)
00-111

Balance (Column 2)
='C:\Financial Sheets\[00-111.xlsx]Phase_21'!$H$1

As you can see the spreadsheets are named after the Project ID number. What I would like to do, if possible, is instead of typing in the name of the spreadsheet for each project in column 2 (Balance), I could somehow reference the cell value in column 1 (Project ID). Something like this:

Project ID (Column 1)
00-111

Balance (Column 2)
='C:\Financial Sheets\["A2".xlsx]Phase_21'!$H$1

where A2 is the cell of the Project ID.

If this is a possibility, would the cell value change when I drag copy the cell (for example would it change from A2 to A3)?

Any advice is much appreciated.

Thanks in advance,

Jeremy

Since you are dealing with workbooks that are probably not going to be open... then I would suggest you download a free adding called Morefunc.xll from here:
Morefunc.xll

and use Indirect.Ext() function.

e.g.
=INDIRECT.EXT("'C:\Financial Sheets\["&A2&".xlsx]Phase_21'!$H$1")

Microsoft has the INDIRECT() function but only works on open workbooks or within the workbook itself.

Also note, that the Morefunc.xll addin allows you to embed the function into the workbook so that other users don't have to install the addin to use the workbook.

Check under Tools|Morefunc|Embed Morefunc in the workbook.