+ Reply to Thread
Results 1 to 10 of 10

VLOOKUP - reference an external table array using formula

  1. #1
    Registered User
    Join Date
    10-29-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    VLOOKUP - reference an external table array using formula

    Every month we create Excel project reports using data provided in an external workbook that always resides in the same relative folder position. I want to use VLOOKUP (lookup_value,table_array,col_index_num,range_lookup) but with a formula in "table_array" to obtain the necessary data without needing to open the other workbook.

    I can use:

    =LEFT(CELL("filename"),(FIND("[",CELL("filename"))-1))&"Finance Supplement\[Fin support data.xlsx]SSRS '!$A$2:$V$80"

    to generate the full path to the array.

    Is it possible to use this as an argument for table_array or am I breaking an obvious rule somewhere? I have tried all kinds of formats without success and I know Indirect() is an option but would prefer the other workbook not need to be opened.

    Help really appreciated.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VLOOKUP - reference an external table array using formula

    You can install the free Excel Add-in called MoreFunc which gives you 100s of new functions. One addition in INDIRECT.EXT() which and indirect that does work on closed workbooks.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    10-29-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: VLOOKUP - reference an external table array using formula

    Our It department is deploying Morefunc to users. I really would like to know if I was trying to do something illegal by inserting the formula in the VLOOKUP argument.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VLOOKUP - reference an external table array using formula

    It is common and normal to replace a single parameter in a formula with another formula that "Creates" that value for you.

  5. #5
    Registered User
    Join Date
    10-29-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: VLOOKUP - reference an external table array using formula

    I have installed Morefunc and have INDIRECT.EXT working but not as I need it to. The calling worksheet needs to reference 3 worksheets of a closed workbook. The paths of the three arrays are generated by formulae in cells C84, C85 and C86 and have been verified as correct by cutting and pasting the resulting text into their VLOOKUP arguments. The INDIRECT.EXT(C84) works fine and pulls the correct data in five VLOOKUP functions. However, when I try to use it for VLOOKUPs using INDIRECT.EXT(C85) and C(86) it gives a #REF! error.

    Is INDIRECT.EXT limited in the number of accesses it can make in a workbook?

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VLOOKUP - reference an external table array using formula

    No, let's see your full formula. Be sure to explain the exact text in reference cell and show the required string result needed.

  7. #7
    Registered User
    Join Date
    10-29-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: VLOOKUP - reference an external table array using formula

    Thanks for taking the time.

    The formulae in cells C84, C85 and C86 are:

    ="'"&LEFT(CELL("filename"),(FIND("[",CELL("filename"))-1))&"Finance Supplement\[Fin support data.xlsx]SSRS '!$A$2:$V$80"
    ="'"&LEFT(CELL("filename"),(FIND("[",CELL("filename"))-1))&"Finance Supplement\[Fin support data.xlsx]Primavera'!$A$2:$AY$81"
    ="'"&LEFT(CELL("filename"),(FIND("[",CELL("filename"))-1))&"Finance Supplement\[Fin support data.xlsx]Previous month Actuals'!$A$2:$AY$82"

    This yields the following paths, respectively, which I have cut and pasted into a VLOOKUP successfully:

    'T:\Project Review Data\2012\10 Oct\Finance Supplement\[Fin support data.xlsx]SSRS '!$A$2:$V$80
    'T:\Project Review Data\2012\10 Oct\Finance Supplement\[Fin support data.xlsx]Primavera'!$A$2:$AY$81
    'T:\Project Review Data\2012\10 Oct\Finance Supplement\[Fin support data.xlsx]Previous month Actuals'!$A$2:$AY$82

    The following VLOOKUP formulae work:

    =VLOOKUP($D$7,INDIRECT.EXT(C84),22,FALSE)/1000
    =VLOOKUP($D$7,INDIRECT.EXT(C84),17,FALSE)/1000

    The following, which are in adjacent cells, do not:

    =VLOOKUP($D$7,INDIRECT.EXT(C86),51,FALSE)/1000
    =(VLOOKUP($D$7,INDIRECT.EXT(C85),24,FALSE)/1000)+(VLOOKUP($D$7,INDIRECT.EXT(C85),26,FALSE)/1000)

    Again, I appreciate your time.

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VLOOKUP - reference an external table array using formula

    Using the EVALUATE FORMULA function, look at what the INDIRECT() portion of the formula is resolving to. If there is even 1 character different, it won't work.

    1) Test your function using INDIRECT() with both workbooks open until it works correctly.
    2) Close the other workbook and change the INDIRECT() to INDIRECT.EXT

  9. #9
    Registered User
    Join Date
    10-29-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: VLOOKUP - reference an external table array using formula

    Quote Originally Posted by JBeaucaire View Post
    Using the EVALUATE FORMULA function, look at what the INDIRECT() portion of the formula is resolving to. If there is even 1 character different, it won't work.

    1) Test your function using INDIRECT() with both workbooks open until it works correctly.
    2) Close the other workbook and change the INDIRECT() to INDIRECT.EXT
    I completely understand that and actually I have already "evaluated". Changing all to INDIRECT with both files open works fine.

    For the two that don't work INDIRECT.EXT cannot evaluate the path even though the path is correctly calculated.

    This is why I am asking if there are limitations to how many INDIRECT.EXT calls can be made to an external file.

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VLOOKUP - reference an external table array using formula

    No, Excel handles each function call independently, if you were having resources issues Excel would tell you that.

    I know this is a widely used ADDIN, been around for over a decade, so it's usable. But honestly I don't do indirect referencing of closed workbooks. I import all incoming data into a database Excel file and run all my data-mining against that consolidated database... mostly for the very issues you're struggling with. It takes a LOT of plumbing to do the way you're trying to do. More plumbing/stress/maintenance than I allow in my projects.

    You may want to Google up on MOREFUNC and see what others have experienced.

+ 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