Referencing a cell in another workbook; Formula from other cell references:

[SevenOut]

I'm trying to reference a cell in another workbook stored in a different directory. Easy enough. However I am attempting the following:
I have a table, Col. A is a list of names. Col. B is where I need to reference a cell in the other workbook.
What I need is a to auto create the reference based on the name in Col. A so if the names change, so does the reference address.

Name
Doe, J.

Address to other workbook:
C:\Users\SOMEBODY\Management\Admin\TargetWorkbook.xlsm
(This workbook has a worksheet for each name, such as 'Doe, J.')
Always referencing A1

Normally this would be something like '=C:\Users\SOMEBODY\Management\Admin\[TargetWorkbook.xlsm]Doe, J.'$A$1
I need the "Doe, J." to change depending on the name in Col. A and the rest to remain static.

 

[SevenOut]

Not sure if this is the best method, but I think this works:
=INDIRECT("'C:\Users\SOMEBODY\Management\Admin\Work\[TargetWorkbook.xlsm]"&A5&"'"&"!$I$1")

 

[SevenOut]

This is only working for an open workbook. Anyone know how to change it to reference a closed workbook?

 

[SevenOut]

Looks like a solution:
=IFERROR(VLOOKUP(A1,' C:\Users\SOMEBODY\Management\Admin\Work\[TargetWorkbook.xlsm]Report'!$H$4:$I$20,2,0),"")

Report happens to be a worksheet in the target workbook with names and desired values of $I$1.