+ Reply to Thread
Results 1 to 3 of 3

Using text in cells to build a link to within another file

  1. #1
    Jon
    Guest

    Using text in cells to build a link to within another file

    BACKGROUND:

    I have a file called summary.xls and 80 data files called data1.xls,
    data2.xls, etc. Let's say that in the summary.xls file, cells A1-A80 contain
    the text "data1.xls", "data2.xls", etc.

    I'd like cells B1-B80 of summary.xls to equal cell C50 on Sheet1 of
    data1.xls, cell C50 on Sheet1 of data2.xls, etc. One way to do this is to
    type "=" into A2 of summary.xls and then open the file data1.xls and click in
    cell C50 of Sheet1. Then repeat this process for all 80 files. Obviously,
    that's a lot of work.


    MY QUESTION:

    I'd like to build a formula to put in cells B1-B80 that will pull the
    filename from column A and create a formula like:

    = '[data1.xls]Sheet1'!$C$50

    To do this, I tried just putting the cell location in place of the
    "data1.xls" in the example above so it looked like:

    = '[A1]Sheet1'!$C$50

    But the result was Excel trying to open a file called "A1". I also tried
    converting the "A1" string above by putting:

    = [T(A1)]Sheet1'!$C$50

    But then Excel just tried to open a file called "T(A1)".

    There must be a way to do this. Any ideas?

  2. #2
    Harlan Grove
    Guest

    Re: Using text in cells to build a link to within another file

    Jon wrote...
    ....
    >There must be a way to do this. Any ideas?


    See

    http://groups-beta.google.com/group/...5?dmode=source

    (or http://makeashorterlink.com/?N12F25A8B ).


  3. #3
    RagDyer
    Guest

    Re: Using text in cells to build a link to within another file

    Check out this old post for an expansion of Harlan's first example, which I
    use extensively in a large dB type WB.

    http://tinyurl.com/8v8e5

    The end of the thread is the pertinent post.
    --
    HTH,

    RD
    ==============================================
    Please keep all correspondence within the Group, so all may benefit!
    ==============================================



    "Harlan Grove" <hrlngrv@aol.com> wrote in message
    news:1123011187.248998.63210@g47g2000cwa.googlegroups.com...
    > Jon wrote...
    > ...
    > >There must be a way to do this. Any ideas?

    >
    > See
    >
    >

    http://groups-beta.google.com/group/...5?dmode=source
    >
    > (or http://makeashorterlink.com/?N12F25A8B ).
    >



+ 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