+ Reply to Thread
Results 1 to 3 of 3

Referencing a filename in a formula

Hybrid View

Guest Referencing a filename in a... 05-11-2006, 01:15 PM
Guest RE: Referencing a filename in... 05-11-2006, 03:00 PM
Guest RE: Referencing a filename in... 05-12-2006, 02:50 AM
  1. #1
    ERR229
    Guest

    Referencing a filename in a formula

    I have a file containing a list of names in column A and a lookup to
    individual data files in column B

    John Smith =Vlookup(A1,F:\Data\[John Smith]Sheet1!$A$1:$D$50,3,false)
    Jane Doe =Vlookup(A1,F:\Data\[Jane Doe]Sheet1!$A$1:$D$50,3,false)

    The players sometimes change, so the reference to the file changes. I would
    like to be able to pull the filename reference in the formula from the name
    in column A. So if I change John Smith to Ted Nugent, the formula
    automatically references Ted Nugent's file.

    Does anyone know if this can be done?

    Thanks in advance for your help!

    --ERR

    --
    ERR229

  2. #2
    kassie
    Guest

    RE: Referencing a filename in a formula

    Try using INDIRECT. =VLOOKUP(A1,F:\Data\&INDIRECT(<Cell ref for John
    Smith>)&Sheet1!$A$1:$D$50,3,FALSE).

    I don't have another drive to test this, but it might work. I tested with
    range names on the same workbook, and it worked fine.

    "ERR229" wrote:

    > I have a file containing a list of names in column A and a lookup to
    > individual data files in column B
    >
    > John Smith =Vlookup(A1,F:\Data\[John Smith]Sheet1!$A$1:$D$50,3,false)
    > Jane Doe =Vlookup(A1,F:\Data\[Jane Doe]Sheet1!$A$1:$D$50,3,false)
    >
    > The players sometimes change, so the reference to the file changes. I would
    > like to be able to pull the filename reference in the formula from the name
    > in column A. So if I change John Smith to Ted Nugent, the formula
    > automatically references Ted Nugent's file.
    >
    > Does anyone know if this can be done?
    >
    > Thanks in advance for your help!
    >
    > --ERR
    >
    > --
    > ERR229


  3. #3
    Mike McLellan
    Guest

    RE: Referencing a filename in a formula

    If the other files are closed then you will need to use INDIRECT.EXT (see
    http://xcell05.free.fr/english/)

    "kassie" wrote:

    > Try using INDIRECT. =VLOOKUP(A1,F:\Data\&INDIRECT(<Cell ref for John
    > Smith>)&Sheet1!$A$1:$D$50,3,FALSE).
    >
    > I don't have another drive to test this, but it might work. I tested with
    > range names on the same workbook, and it worked fine.
    >
    > "ERR229" wrote:
    >
    > > I have a file containing a list of names in column A and a lookup to
    > > individual data files in column B
    > >
    > > John Smith =Vlookup(A1,F:\Data\[John Smith]Sheet1!$A$1:$D$50,3,false)
    > > Jane Doe =Vlookup(A1,F:\Data\[Jane Doe]Sheet1!$A$1:$D$50,3,false)
    > >
    > > The players sometimes change, so the reference to the file changes. I would
    > > like to be able to pull the filename reference in the formula from the name
    > > in column A. So if I change John Smith to Ted Nugent, the formula
    > > automatically references Ted Nugent's file.
    > >
    > > Does anyone know if this can be done?
    > >
    > > Thanks in advance for your help!
    > >
    > > --ERR
    > >
    > > --
    > > ERR229


+ 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