Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3171
  • Last Modified:

VLOOKUP to a Closed Workbook

Does anyone have a good simple example file that does a VLOOKUP to an external unopened workbook?  I am looking to pull one value to an Activesheet, but do not want to open the huge file that the value is in...  Any help would be appreciated.
0
RWayneH
Asked:
RWayneH
  • 9
  • 6
  • 2
  • +4
1 Solution
 
Brian BIndependant Technology ProfessionalCommented:
The easiest way to do it:

1. Open the source file.
2. Build you vlookup.
3. Close the source file.

The links names will changes themselves to the correct name and path automatically. As long as nobody moves it, you'll never have to open the source sheet again.
0
 
Mike in ITIT System AdministratorCommented:
This is pretty easy as long as you know the filename and the sheet name, and they won't change or be moved.

=VLOOKUP(B1,'[Workbookname.xlsx]SheetName'!$B$2:$C$62,2,TRUE)

Open in new window


Obviously you will replace the `$B$2:$C$62` with the location of the data you are looking up and the "2" with what column you want returned. And "B1" with what column in the active workbook you are testing against.

Brian makes it easy to create the formula by opening the extra workbook and using the Formulas tab to insert a formula excel will do the typing for you. Make sure your last value in the VLOOKUP is "TRUE" as using "FALSE" often gives undesired results as it looks for an exact match while "TRUE" looks for one that is a close match
1
 
RWayneHAuthor Commented:
The goal is not to have to open the source file.  Do you have any sample files that could attached?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
as stated above, once the link is in, the course file does not have to be opened again -- just easier to get the link in when it is. Using the example above:
=VLOOKUP(B1,'c:\path\[Workbookname.xlsx]SheetName'!$B$2:$C$62,2,TRUE)

Open in new window

I disagree about using TRUE though. I generally want an EXACT match so use FALSE for the argument that says Excel can approximate it. TRUE is used when you are looking up a value in a range of numbers, such as a tax table. In those cases, the lookup column also needs to be sorted.
0
 
byundtCommented:
I respectfully differ with Michael McNair over whether it is better to use TRUE or FALSE as the last parameter in a VLOOKUP. By far, the more common usage is with FALSE as the fourth parameter, as you are looking for an exact match for the first parameter.

Using TRUE is done mostly when you are doing bracket price lookups, such as with income tax tables, or when the unit price of a commodity varies with how many you buy. In such cases, the leftmost column in the lookup table must show the bottom of each bracket, and the brackets must be listed in ascending order.

I have occasionally recommended VLOOKUP with TRUE as the fourth parameter when I needed an exact match and knew that it would always be in the lookup table. The benefit here is more efficient searching if you have previously sorted sorting the lookup table by the first column values. By using TRUE as the fourth VLOOKUP parameter, VLOOKUP performs a binary search for the right row, and can find the right answer in a 1000 row table with only 11 tests. In contrast, the same VLOOKUP search with FALSE as the fourth parameter will test 500 times (on average) before finding a match.

If you like the speed of TRUE, but need to allow for the possibility of not matching, then you could use two VLOOKUP calls:
=IF(VLOOKUP("LookFor", A$1:A$1000,1)="LookFor",VLOOKUP("LookFor",A$1:C$1000,3),"Not found")
You will note that the above formula omits TRUE as the fourth VLOOKUP parameter; TRUE is assumed in such omissions.

Finally, you need to know the path to the workbook if you don't want to open it while performing a VLOOKUP. Michael McNair's formula should therefore be:
=VLOOKUP(B1,'C:/Some Folder/Some subfolder/[Workbookname.xlsx]SheetName'!$B$2:$C$62,2,FALSE)

Open in new window


Although I could certainly attach two workbooks as requested to illustrate the results of this process, the formulas probably won't work when you download the workbooks. Unless you put the closed file in the same folder & path that file lives in on my computer, the VLOOKUP formula in the open workbook will return an error value until you correct the path.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
I agree with byundt ... and I think he also mean to use \ in the path not /
0
 
byundtCommented:
Crystal,
Right you are on the need for a \ as a path separator character.

Thanks!
Brad
0
 
ProfessorJimJamCommented:
@RWayneH

please see my answer to similar question at post here

i believe, that is the best ready made example that can get you started.

download the two files, one which is a source file and the otherone which has the code in it. change the file path and the see the example of vlookup on the closed file. it will work without opening the source file.
0
 
RWayneHAuthor Commented:
ProfessorJimJam ,  thanks for the files.. it definitely is a good example, however I am interested how to rewrite the formula.  I do not want to use the G1 concatenated path ref that it uses..  How would the formula look if I wanted to just write it out using a named range?  "MatlDB"

Your example uses:  =VLOOKUP(A8,IndirectEx($G$1),2,FALSE)   I want to remove the ($G$1)  but when I try to put in:
C:\_Excel\ExcelExamples\VLOOKUP_External_Sample\[vlookup_with_indirect_source.xlsx]Bikes!$A$3:$B$13


=VLOOKUP(A8,IndirectEx(C:\_Excel\ExcelExamples\VLOOKUP_External_Sample\[vlookup_with_indirect_source.xlsx]Bikes!MatlDB),2,FALSE)
it fails.  Having issues rewriting the formula, any ideas?

I am also confused about whether I really need to use IndirectEx?  Sounds like from the other posts, that is not necessary?  Would like some more guidance please.
0
 
RWayneHAuthor Commented:
Because the following seems to work.  However it is not using InDirectEx    Is even needed?

=VLOOKUP(A12,'C:\_Excel\ExcelExamples\VLOOKUP_External_Sample\[vlookup_with_indirect_source.xlsx]Bikes'!MatlDB,2,FALSE)
0
 
ProfessorJimJamCommented:
Hi
If you don't want the indirectex to refer to a cell instead you can put the path inside the double quote " full path and address"
However please note that you must use theindirectex UDF , if you remove the UDF it will not work , it may return the data which is saved in memory, but if you change the path it will not work. So you need to have the UDF Indirectex and either use cell eefer CEO or hardcode the full path and address inside it wrapped by double quote.
0
 
RWayneHAuthor Commented:
I am still having issues...  I added the InDirectEx and it is not working anymore??

=VLOOKUP(A12,IndirectEx'C:\_Excel\ExcelExamples\VLOOKUP_External_Sample\[vlookup_with_indirect_source.xlsx]Bikes'!MatlDB,2,FALSE)

Still need some help writing the whole thing out..   does not seem to want to work with the IndirectEx in it.
0
 
RWayneHAuthor Commented:
Also still not sure I even need to use IndirectEx??  All I need is the value of the vlookup cell, ??  IndirectEx is new to me.
0
 
RWayneHAuthor Commented:
This is what I landed on:
=VLOOKUP(A6,IndirectEx("'C:\_Excel\ExcelExamples\VLOOKUP_External_Sample\[vlookup_with_indirect_source.xlsx]Bikes'!MatlDB"),2,FALSE)

Open in new window


Seems to have worked.. now just not understanding the need for or not the need for IndirectEx..
0
 
ProfessorJimJamCommented:
@RWayneH

INDIRECTEX is the main driver for fetching the data from the closed workbook.  it is not the standard INDIRECT function, it is a user defined function meaning it is a created function. if you press Alt + F11 you will see the UDF code .   if you eliminate  INDIRECTEX then it will not work.

i am glad that it worked for you.
0
 
RWayneHAuthor Commented:
Well I am not so sure I am out of the woods yet...  I went in and chg'd a value on the external sheet and the opened sheet is not picking up the new values...  Even after I retype the formula?  How would I get the data refreshed?  When I do a recalc or a refresh all it is not working.  Just shows what was there before?  Is that normal?  I am concerned that when a new external file replaces the old that we are not going to get proper data?  I am planning on entering the formula via vba and copying the formula down, then copy, special paste values so formulas are gone.  Any idea why they will not refresh?
0
 
ProfessorJimJamCommented:
@RWayneH

if you change value in external workbook,. then you need save it and close it and then open the workbook with formula and the changed values should reflect in the workbook with formula.

the formula works when you change something in ext workbook and then save and close it and open the workbook with formula again.

also, make sure that your macro is enabled when you open the workbook with formula.
0
 
RWayneHAuthor Commented:
Ah, so the trick is that you have to close and reopen the file for the new values to appear...  I see.
0
 
ProfessorJimJamCommented:
yes.
0
 
Rob HensonIT & Database AssistantCommented:
VLOOKUP works on closed files without an INDIRECT function or UDF if the file path, name or lookup range are fixed.

If those parameters are not fixed then an INDIRECT function can be used to create the name and/or path but INDIRECT only works on open workbooks, hence ProfJJ suggestion to use a UDF to overcome the restriction of INDIRECT.

If you create a link to a smaller file, you can then use the same syntax "Path/File/Sheet/Range" to amend for the file you don't want to open, so long as you know the parameter values to enter.
0
 
RWayneHAuthor Commented:
Very good example of VLOOKUP to an external unopened file.  It took a little playing with to figure it out but once I did.. it worked nicely.  Thanks for the help.
0
 
ProfessorJimJamCommented:
You are welcome.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 9
  • 6
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now