+ Reply to Thread
Results 1 to 6 of 6

[SOLVED] How do I use a cell value as the filename in an external link?

  1. #1
    wattkisson
    Guest

    [SOLVED] How do I use a cell value as the filename in an external link?

    I want to set up a workbook with several values from linked workbooks. I
    would like to enter a value in column A and then use that value as the
    filename in the rest of the cells that link to the external workbook.
    For example:

    I want to enter A122 into colum A - and have the cell in column B pick that
    value up and link to a cell in an external workbook with that name

    Obviously, the following function links correctly to the external file and
    returns the correct value:
    ='G:\Local files\Sample Tracking\[A122.xls]Sample Request'!$E$2

    But, I do not want to have to change the syntax in a large number of cells
    everytime I add to the list. I only want to enter the value (A122) in the
    first cell and have the others retrieve linked values correctly.
    In other words, I want a dynamic, external link.

    Thanx in advance for any help.

  2. #2
    Dave Peterson
    Guest

    Re: How do I use a cell value as the filename in an external link?

    You'd want to use the =indirect() worksheet function. But that doesn't work
    with closed files.

    Harlan Grove wrote a UDF called that will retrieve the value from a closed
    workbook.

    You can find the function at Harlan's FTP site:
    ftp://members.aol.com/hrlngrv/

    wattkisson wrote:
    >
    > I want to set up a workbook with several values from linked workbooks. I
    > would like to enter a value in column A and then use that value as the
    > filename in the rest of the cells that link to the external workbook.
    > For example:
    >
    > I want to enter A122 into colum A - and have the cell in column B pick that
    > value up and link to a cell in an external workbook with that name
    >
    > Obviously, the following function links correctly to the external file and
    > returns the correct value:
    > ='G:\Local files\Sample Tracking\[A122.xls]Sample Request'!$E$2
    >
    > But, I do not want to have to change the syntax in a large number of cells
    > everytime I add to the list. I only want to enter the value (A122) in the
    > first cell and have the others retrieve linked values correctly.
    > In other words, I want a dynamic, external link.
    >
    > Thanx in advance for any help.


    --

    Dave Peterson

  3. #3
    Registered User
    Join Date
    03-31-2004
    Location
    Toronto, Canada
    MS-Off Ver
    2003/2007
    Posts
    36
    following is a code i am using in one of my spreadsheet to do similar thing. the only difference might the fact that i am retreiving same cell (A3) form each file.

    Please Login or Register  to view this content.
    Hope this helped

    Keyur

  4. #4
    Earl Kiosterud
    Guest

    Re: How do I use a cell value as the filename in an external link?

    Wattkisson,

    You could use the INDIRECT function, but the linked workbook must be open.
    Other than that, I think your only solution will be to create the formula
    with a macro.

    Often a project is split into separate workbooks (and separate worksheets),
    when it should be in fewer (or one) workbook. Consider that carefully; it
    might be what you need.
    --
    Earl Kiosterud
    www.smokeylake.com/
    -------------------------------------------

    "wattkisson" <[email protected]> wrote in message
    news:[email protected]...
    >I want to set up a workbook with several values from linked workbooks. I
    > would like to enter a value in column A and then use that value as the
    > filename in the rest of the cells that link to the external workbook.
    > For example:
    >
    > I want to enter A122 into colum A - and have the cell in column B pick
    > that
    > value up and link to a cell in an external workbook with that name
    >
    > Obviously, the following function links correctly to the external file and
    > returns the correct value:
    > ='G:\Local files\Sample Tracking\[A122.xls]Sample Request'!$E$2
    >
    > But, I do not want to have to change the syntax in a large number of cells
    > everytime I add to the list. I only want to enter the value (A122) in the
    > first cell and have the others retrieve linked values correctly.
    > In other words, I want a dynamic, external link.
    >
    > Thanx in advance for any help.




  5. #5
    wattkisson
    Guest

    Re: How do I use a cell value as the filename in an external link?

    Thanks. INDIRECT did the trick. Now I will try to get around the open
    workbook updating issues as you suggested.

    "Dave Peterson" wrote:

    > You'd want to use the =indirect() worksheet function. But that doesn't work
    > with closed files.
    >
    > Harlan Grove wrote a UDF called that will retrieve the value from a closed
    > workbook.
    >
    > You can find the function at Harlan's FTP site:
    > ftp://members.aol.com/hrlngrv/
    >
    > wattkisson wrote:
    > >
    > > I want to set up a workbook with several values from linked workbooks. I
    > > would like to enter a value in column A and then use that value as the
    > > filename in the rest of the cells that link to the external workbook.
    > > For example:
    > >
    > > I want to enter A122 into colum A - and have the cell in column B pick that
    > > value up and link to a cell in an external workbook with that name
    > >
    > > Obviously, the following function links correctly to the external file and
    > > returns the correct value:
    > > ='G:\Local files\Sample Tracking\[A122.xls]Sample Request'!$E$2
    > >
    > > But, I do not want to have to change the syntax in a large number of cells
    > > everytime I add to the list. I only want to enter the value (A122) in the
    > > first cell and have the others retrieve linked values correctly.
    > > In other words, I want a dynamic, external link.
    > >
    > > Thanx in advance for any help.

    >
    > --
    >
    > Dave Peterson
    >


  6. #6
    wattkisson
    Guest

    Re: How do I use a cell value as the filename in an external link?

    Thanks. INDIRECT did the trick.

    "Keyur" wrote:

    >
    > following is a code i am using in one of my spreadsheet to do similar
    > thing. the only difference might the fact that i am retreiving same
    > cell (A3) form each file.
    >
    >
    > Code:
    > --------------------
    > Private Sub Worksheet_Change(ByVal Target As Range)
    > Dim flnm As String
    > Application.EnableEvents = False
    > If Target.Column = 1 And IsEmpty(Target.Value) = False Then
    > flnm = "C:\Documents and Settings\kapatel\Desktop\" & Target.Value & ".xls"
    > If Dir(flnm) <> "" Then
    > Me.Cells(Target.Row, 2).Formula = "='C:\Documents and Settings\kapatel\Desktop\[" & Target.Value & ".xls]Details'!$A$3"
    > Else
    > Me.Cells(Target.Row, 2).Value = "File not found."
    > End If
    > ElseIf IsEmpty(Target.Value) = True Then
    > Me.Cells(Target.Row, 2).Value = "File not found."
    > End If
    > Application.EnableEvents = True
    > End Sub
    >
    > --------------------
    >
    >
    > Hope this helped
    >
    > Keyur
    >
    >
    > --
    > Keyur
    > ------------------------------------------------------------------------
    > Keyur's Profile: http://www.excelforum.com/member.php...fo&userid=7786
    > View this thread: http://www.excelforum.com/showthread...hreadid=385705
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1