Construct a link to another workbook with Concatenate?

[Shoebird]

I am trying to construct a dynamic link based upon the concatenation of variables contained in other cells.
A1="AHU RTU4.xlsx"
A2="//sharepoint/support/Arizona/2016"

The concatenation would look like the following:
=concatenate(a2,"[",a1,"]Table'!$B$2")

This is where it becomes tricky. How do I automatically extract the information from this location without having to use the hyperlink function since this is a manual step?

Thank you all in advance.

 

[Hui]

=Indirect(concatenate(a2,"[",a1,"]Table'!$B$2"))

 

[Shoebird]

=Indirect(concatenate(a2,"[",a1,"]Table'!$B$2"))

Hui, you are awesome!

For some reason though, Indirect is not working for me. I did a direct copy/paste from the source document into the workbook I am working on and closed the source workbook to be able to verify the correct address. Constructed Indirect/Concatenate so they are identical and I get a #ref error. ???

 

[Shoebird]

Hui, you are awesome!

For some reason though, Indirect is not working for me. I did a direct copy/paste from the source document into the workbook I am working on and closed the source workbook to be able to verify the correct address. Constructed Indirect/Concatenate so they are identical and I get a #ref error. ???

One other thing to note. The source file starts with 'http://sharepoint/...

Not sure if this makes a difference.

 

[Hui]

Can you post the file with the real links?

 

[Chihiro]

FYI - Indirect function will not work with closed workbook.

I'd use MS Query or PowerQuery to import data... or VBA using ADODB.