How to force cell formulas formed by concatenating text strings and cell
references and acting as text, to behave like formulas? The problem I have
is that when I use the INDIRECT function, the source workbook must be open,
but there are just too many of them to make it a practical solution...

Here is an example of the formula I'd like to have:
='W:\Company\Division\TestsResults\[Test001.xls]Sample_ID'!$A$1
as a result of the following concatentaion:
="'W:\Company\Division\TestsResults\["&$A10&".xls]Sample_ID'!$A$1"
where cell A10 contains text Test001

Is there a convenient solution that would allow copying and dragging this
formula to generate new entries that would get recalculated based on the
value in column A?

z.entropic