For instance, on my current worksheet I want cell D7 to display the
value of Row 1 Column 2 of the worksheet "TEST" found in any of the
workbooks I put into cell B7.


Pardon, that should read Row 2, Column 1 if I'm talking about $A$2
further down in the post!