Join text & cell reference in a link.

This is a variant of paste links.

I have a long list of one-word names in column A of my active sheet (Ape, Bear, Cat, etc). Elsewhere on my hard drive I have a series of files called Ape.xlsx, Bear.xlsx, Cat.xlsx, etc.

In column B of my active sheet I want to return the value in a fixed cell (e.g. B4) from each of these files. Yes, I can paste a link once and then Ctrl+H to change the file name, but it would be a really neat solution to be able to combine the value in the appropriate row of column A, with the rest of the path, to make the procedure much faster less prone to error caused by my ten-thumb typing skills.


Row...................Col A..................Col B
1.......................Ape...................='C:\Users\Glenn\Desktop\[Ape.xlsx]Sheet1'!$B$4

I've tried ="Text"&a1&"text" and lots of variants, but i'm goofing up somehwere.

The formula bar shows:

="'C:\Users\Glenn\Desktop\["&A2&".xlsx]Sheet1'!$B$2"


The cell content displays (seemingly correctly), as a string:

'C:\Users\Glenn\Desktop\[Ape.xlsx]Sheet1'!$B$4,

but doesn't return the value in B4 of Ape.xlsx. If this can be done, how can this be done?