• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Construct a link to another workbook with Concatenate?

Shoebird

Member
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.
 

Shoebird

Member
=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

Member
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.
 

Chihiro

Excel Ninja
FYI - Indirect function will not work with closed workbook.

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