Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Dynamic file selection and vlookup fuction

  1. #1
    Board Regular
    Join Date
    Feb 2012
    Location
    IN
    Posts
    230
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Dynamic file selection and vlookup fuction

    Hi Team,

    I have one requirement hope can anyone help with the same.

    I will receive one master file which contains the table and it's values and I have another working sheet where I will update the require details. Every time I will copy the entire master data into working file in order to extract the values by using vlookup function. Plz note that the master file has different names each time. So, that if I use vlookup by taking master data path but not getting the results.

    I am looking one macro code to select file (master data) and paste the values based on vlooup references in workings sheets. Note this master data file name may change but reference range always same.

    Thanks you.

  2. #2
    Board Regular etaf's Avatar
    Join Date
    Oct 2012
    Location
    UK, West Sussex
    Posts
    3,156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic file selection and vlookup fuction

    you could use an indirect function
    so if you put the name of the file into a cell somewhere and then reference that cell as part of the vlookup

    so if you put the name of the master data file into a cell - say AA1
    then you can use this as the vlookup
    =(VLOOKUP(B46,INDIRECT("'["&$AA$1&".xlsx]sheet1'!$A$1:$B$12"),2,FALSE))

    each time the master file changes- just copy to the same directory as your main working file
    change the name in cell AA1 or whatever cell works for you
    and the vlookup should work and pull in the required information

    in this example - I have a separate workbook in the same directory called "values" and have in column A 1-6 and column B A-F

    Sheet1

     BCDE
    43Name of master file  Values 
    44    
    45    
    461a  
    473c  
    485e  
    49    

    Spreadsheet Formulas
    CellFormula
    C46=(VLOOKUP(B46,INDIRECT("'["&$D$43&".xlsx]sheet1'!$A$1:$B$12"),2,FALSE))


    Excel tables to the web >> Excel Jeanie HTML 4

  3. #3
    Board Regular
    Join Date
    Feb 2012
    Location
    IN
    Posts
    230
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic file selection and vlookup fuction

    I not sure why I am getting reference error.....below is my example....

    =VLOOKUP(J2,INDIRECT("'["&$B$9&"]Workings'!$A:$M"),8,0)

    Path = C:\Data format\test1.xlsm and sheet name is "workings"

    plz help on this....

  4. #4
    Board Regular etaf's Avatar
    Join Date
    Oct 2012
    Location
    UK, West Sussex
    Posts
    3,156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic file selection and vlookup fuction

    Your adding Square brackets to all the path

    whats in B9 ?


    if this is in B9
    C:\Data format\test1.xlsm

    then you need [] around the file name

    format is

    'C:\Users\wayne\Documents\0. TSG\Excel\indirect\Indirect from workbook\[settings.xlsx]sheet1'!c30

    where you need the ' if there are any spaces in the names

    so to reproduce
    C:\Data format\test1.xlsm and use the sheet Workings'!$A:$M"

    'C:\Data format\[test1.xlsm]Workings'!$A:$M"

    so the indirect will depend on what you have in cell
    B9

    assume its just test1.xlsm

    then

    "'C:\Data format\[" & B9 & "]Workings'!$A:$M"

    indirect (
    "'C:\Data format\[" & B9 & "]Workings'!$A:$M" )


    =VLOOKUP(J2, indirect ("'C:\Data format\[" & B9 & "]Workings'!$A:$M" ) ,8,0)

    you need the ' because data format has a space

    the work book
    test1.xlsm needs to be open to reference - this will not work for closed workbooks - which may not be what you want



  5. #5
    Board Regular
    Join Date
    Feb 2012
    Location
    IN
    Posts
    230
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic file selection and vlookup fuction

    Thanks for your help it's working fine....yes I was trying the fuction before opening the file. Is there any other way where I can extract the values apart from this and even if file closed still can able to get the values by using vlooup....

  6. #6
    Board Regular etaf's Avatar
    Join Date
    Oct 2012
    Location
    UK, West Sussex
    Posts
    3,156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic file selection and vlookup fuction

    not with a function , I think you may need VBA code to do that - but not certain

    there is a function -
    Extract data from multiple cells of closed Excel files

    but last time I tried it - it did not work on excel 2010

  7. #7
    Board Regular etaf's Avatar
    Join Date
    Oct 2012
    Location
    UK, West Sussex
    Posts
    3,156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic file selection and vlookup fuction

    i have now added the three addins for direct.ext and got it to work

    what version of excel are you running ?

  8. #8
    Board Regular
    Join Date
    Feb 2012
    Location
    IN
    Posts
    230
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic file selection and vlookup fuction

    oh...ok, I use 2007 version....

  9. #9
    Board Regular etaf's Avatar
    Join Date
    Oct 2012
    Location
    UK, West Sussex
    Posts
    3,156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic file selection and vlookup fuction

    you could try indirect.ext - i did get indirect.ext working using the link I posted - which also covers 2007 version

  10. #10
    New Member
    Join Date
    Mar 2013
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic file selection and vlookup fuction

    Hi,

    It seems to work fine for me. May be the Morefunc add-in has not be installed properly. For Excel 2010, the steps for installation are mentioned in step 4 of the same link. Even if after proper installation, it is not working fine (same problem being faced by Simon in the Comments section of the link), then restarting MS Excel may solve the problem.

Some videos you may like

User Tag List

Tags for this Thread

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
  •