Solved

VLOOKUP to a Closed Workbook

Posted on 2016-11-18
22
183 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
ID: 41893629
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 6

Expert Comment

by:Mike in IT
ID: 41893653
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
ID: 41893791
The goal is not to have to open the source file.  Do you have any sample files that could attached?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 19
ID: 41893956
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 81

Expert Comment

by:byundt
ID: 41893959
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 19
ID: 41893965
I agree with byundt ... and I think he also mean to use \ in the path not /
0
 
LVL 81

Expert Comment

by:byundt
ID: 41893975
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
ID: 41894127
@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
ID: 41895114
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
ID: 41895120
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
ID: 41895142
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
 

Author Comment

by:RWayneH
ID: 41895835
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
ID: 41895850
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
ID: 41895883
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
ID: 41895985
@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
ID: 41896022
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
ID: 41896037
@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
ID: 41896090
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
ID: 41896110
yes.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41896254
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
ID: 41896560
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
ID: 41896985
You are welcome.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
.Range Set 25 77
Excel Question 17 15
Excel file that does not ask to be saved before exiting 6 22
Time difference between dates without weekend 16 25
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

773 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