Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Lookup Fn using a dynamic filename

  1. #1
    Guest

    Default

    I am trying to use lookup on a dynamic file list.
    I have a number of files in a directory, I want to do a lookup in.

    So,

    lookup(1,'[File1]FormX'!$b3:$b1030,'[File1]FormX'!$c3:$c1030)
    lookup(1,'[File2]FormX'!$b3:$b1030,'[File2]FormX'!$c3:$c1030)

    Where file1, file2 etc come from a directory list.

    I can get file1 and file2 etc easily. What I canít seem to do is dynamically create the lookup command!!
    I can create the text of the lookup function, I just cant tell it itís a function rather than text!

    Any help would be most appreciated!

    BJ


  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,049
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-03-13 07:02, Anonymous wrote:
    I am trying to use lookup on a dynamic file list.
    I have a number of files in a directory, I want to do a lookup in.

    So,

    lookup(1,'[File1]FormX'!$b3:$b1030,'[File1]FormX'!$c3:$c1030)
    lookup(1,'[File2]FormX'!$b3:$b1030,'[File2]FormX'!$c3:$c1030)

    Where file1, file2 etc come from a directory list.

    I can get file1 and file2 etc easily. What I canít seem to do is dynamically create the lookup command!!
    I can create the text of the lookup function, I just cant tell it itís a function rather than text!

    Any help would be most appreciated!

    BJ

    Longre's UDF called EVAL might help here.

    For example:

    =EVAL(""&E1&"(A1:A4)")

    where E1 houses the text 'SUM'.

    The formula causes SUM to sum the range A1:A4.

    You can download morefunc that contains EVAL at:

    http://perso/wanadoo.fr/longre/excel/downloads/

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    North Alabama, USA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am not clear on everything, but somethimes you can back in to things and make things work. INDEX() and INDIRECT() come to mind. If you can set the formula up to reference a cell with one of these than you can dynamically change that cell and the formula should continue to work.
    HTH
    Rocky

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,496
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default


    You can use Indirect.
    1. File name in B2
    2. Lookup ranges are named rL

    3. formula

    =VLOOKUP(A3,INDIRECT(B2&".xls!rL"),2,0)

    comments
    - do not have to worry about sheet name
    since the range is named rL in each file
    - to use Indirect, the files must be open


    [ This Message was edited by: Dave Patton on 2002-03-15 20:34 ]

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
  •