Using INDIRECT in Excel 2010 linking to another workbook

Posted on 2012-08-15
Last Modified: 2013-08-04
Good Morning,

I am wondering if anyone can help me please.
I have successfully been able to use the INDIRECT function to access a worksheet within the same workbook using the below syntax:


where A1 contains the name of the worksheet in question.

I would now like to access a worksheet within a different workbook and am trying to use INDIRECT for the same reason.
I have been on a number of sites, including Microsoft's site but the syntax that I am trying to use does not work:

This is my latest syntax:


A1 contains "\\ipaddress\subfolder\file.xlsx"
A2 contains "Sheet1"
A3 contains "B3"

Any help would be most appreciated.

Many thanks
Question by:gcz
    LVL 8

    Assisted Solution

    by:Elton Pascua
    Is the target workbook open?

    If not, below is a workaround to get it from closed workbooks.

    Open in new window

    LVL 24

    Accepted Solution

    The following Function may do the task you need using ADO...

    Function GetValueFromWorkbook(strSource As String, strSheet As String, strRange As String) As String
        Dim adoConnection       As Object
        Dim rcdSource           As Object
        Dim lngLoop             As Long
        Set adoConnection = CreateObject("ADODB.Connection")
        Set rcdSource = CreateObject("ADODB.Recordset")
        adoConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strSource & ";Extended Properties=""Excel 12.0 XML;HDR=NO"";"
        rcdSource.Open "SELECT * FROM [" & strSheet & "$" & strRange & ":" & strRange & "]", adoConnection
        GetValueFromWorkbook = rcdSource(0).Value
        Set rcdSource = Nothing
        Set adoConnection = Nothing
    End Function

    Open in new window

    See example file attached.
    LVL 1

    Author Comment

    Thanks everyone for the replies.

    My target workbook is not open so I will try both options that you suggest and see how I get on.

    Thanks again for yoru replies

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now