+ Reply to Thread
Results 1 to 5 of 5

vlookup to external file with dynamic input

  1. #1
    Registered User
    Join Date
    11-28-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    3

    vlookup to external file with dynamic input

    hi friends,

    I have several files from which I have to look up specific value and append in single file.

    For this I am facing two problems
    1. Many Source Files needs to be opened simultaneously
    2. Dynamic path input is required

    Kindly guide.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: vlookup to external file with dynamic input

    Hi and welcome to the forum

    why do you say they need to be opened? vlookup and index/match (in fact most functions) will work on closed workbooks.

    I suggest you give a bunch more detail on what you have, what you want and how you get that
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    11-28-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: vlookup to external file with dynamic input

    Hi FDibbins,

    Thank you for warm welcome and quick reply. Detailed condition would be as below.

    a) I have sets of <strings> with specific number pattern in FileA.xlsx
    i.e.
    <string> =
    PA52.0_CA42.0_TA56.0
    PA52.0_CA42.0_TA57.0
    PA52.0_CA42.0_TA58.0
    PA52.0_CA42.0_TA59.0
    PA53.0_CA42.0_TA56.0
    PA54.0_CA42.0_TA57.0
    PA55.0_CA42.0_TA58.0
    PA52.0_CA42.0_TA59.0

    total 7Lacs such strings in FileA.xlsx coulmn A

    b) Those <strings> has specific value generated in another file in 9th column which to be looked up. say those file name are with first few latter of strings
    i.e.
    File_PA52.0.xlsx
    File_PA53.0.xlsx
    File_PA54.0.xlsx etc

    I want to write vlookup function in FileA.xlsx such that
    =vlookup($A1,concatenate("'C:\Folder\File_",left($A1,6),".xlsx'!$A:$I",9,FALSE)

    But it returns nothing....

    Waiting for your reply, and excuse and let me know if there are still confusion in above detail.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: vlookup to external file with dynamic input

    When you concatenate (join) different pieces of text (and/or numbers), all you end up with is...more text - which excel cannot use as a reference for anything. What you need, is to use INDIRECT() to convert that text into a reference that excel can then use in formulas.

    However, INDIRECT() does not work on closed workbooks, so for this, you need to install the MOREFUNC add-in - see this link (there are many many more if you google morefunc)
    http://www.ashishmathur.com/tag/morefunc/

  5. #5
    Registered User
    Join Date
    11-28-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: vlookup to external file with dynamic input

    Friend,

    Seems like it will work after soling suggested addin. But I want your further help as in our office external software is not allowed to install

    Can I get it's vba query directly?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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