Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Using a cell to change the filename reference

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would like to be able to use cell (B1) to change the file name referenced in a lookup formula.

    Example: A1 contains a company number "503"

    A2 is a lookup from March's sales file =VLOOKUP($A1,'C:ACCTSALES[March.xls]Sheet1'!$A$4:$M$184,2,FALSE)

    I would like to replace the [March.xls] with a cell reference within my file so that I can change my lookup by changing "March.xls" to "April.xls".

    _________________
    Thanks
    rkeithg

    [ This Message was edited by: rkeithg on 2002-04-10 12:13 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,308
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hiya

    I think you want something like the INDIRECT function. Instead of:

    =VLOOKUP($A1,'C:ACCTSALES[March.xls]Sheet1'!$A$4:$M$184,2,FALSE)

    Try:


    =VLOOKUP($A1INDIRECT(Z1),2,FALSE)

    where Z1 contains the text string: 'C:ACCTSALES[March.xls]Sheet1'!$A$4:$M$184

    I believe the file needs to be open for this to work however, kind of a big drawback.

    Hope that helps somewhat
    Adam


  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,655
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-10 12:12, rkeithg wrote:
    I would like to be able to use cell (B1) to change the file name referenced in a lookup formula.

    Example: A1 contains a company number "503"

    A2 is a lookup from March's sales file =VLOOKUP($A1,'C:ACCTSALES[March.xls]Sheet1'!$A$4:$M$184,2,FALSE)

    I would like to replace the [March.xls] with a cell reference within my file so that I can change my lookup by changing "March.xls" to "April.xls".

    _________________
    Thanks
    rkeithg

    [ This Message was edited by: rkeithg on 2002-04-10 12:13 ]
    Your formula appears to show typos -- It's probably:

    =VLOOKUP($A1,'C:ACCTSALES[March.xls]Sheet1'!$A$4:$M$184,2,FALSE)

    Assuming that B1 houses [April.xls], try:

    =VLOOKUP($A1,INDIRECT("'C:ACCTSALES"&B1&"Sheet1'!$A$4:$M$184"),2,FALSE)

    Aladin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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