Solved

VLOOKUP to a Closed Workbook

Posted on 2016-11-18
22
61 Views
Last Modified: 2016-11-21
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
Comment
Question by:RWayneH
  • 9
  • 6
  • 2
  • +4
22 Comments
 
LVL 23

Expert Comment

by:Brian B
Comment Utility
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
 
LVL 2

Expert Comment

by:Mike in IT
Comment Utility
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
 

Author Comment

by:RWayneH
Comment Utility
The goal is not to have to open the source file.  Do you have any sample files that could attached?
0
 
LVL 18
Comment Utility
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
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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
 
LVL 18
Comment Utility
I agree with byundt ... and I think he also mean to use \ in the path not /
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
Crystal,
Right you are on the need for a \ as a path separator character.

Thanks!
Brad
0
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 500 total points
Comment Utility
@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
 

Author Comment

by:RWayneH
Comment Utility
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
 

Author Comment

by:RWayneH
Comment Utility
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
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:RWayneH
Comment Utility
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
 

Author Comment

by:RWayneH
Comment Utility
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
 

Author Comment

by:RWayneH
Comment Utility
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
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
@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
 

Author Comment

by:RWayneH
Comment Utility
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
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
@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
 

Author Comment

by:RWayneH
Comment Utility
Ah, so the trick is that you have to close and reopen the file for the new values to appear...  I see.
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
yes.
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
 

Author Closing Comment

by:RWayneH
Comment Utility
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
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
You are welcome.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now