Dynamic vlookup from a close workbook
I am trying (very unsuccessfully) to create a vba function that will be like
vlookup but will look in other sheets have 3 argument. My problem is how to pass the reference to the other sheet to the function I am aware I can do this without a function: =VLOOKUP(A1,'C:\Desktop\[test3.xls]Sheet2'!$A$1:$C$18,3,FALSE) But then if I try to do: =VLOOKUP(A1,"'C:\Desktop\"&A2,3,FALSE) A2= [test3.xls]Sheet2'!$A$1:$C$18 It does not work Any idea? Dan |
Dynamic vlookup from a close workbook
I'd try changing
=VLOOKUP(A1,"'C:\Desktop\"&A2,3,FALSE) to =VLOOKUP(A1,indirect("'C:\Desktop\"&A2),3,FALSE) HTH, Barb Reinhardt "Dan" wrote: I am trying (very unsuccessfully) to create a vba function that will be like vlookup but will look in other sheets have 3 argument. My problem is how to pass the reference to the other sheet to the function I am aware I can do this without a function: =VLOOKUP(A1,'C:\Desktop\[test3.xls]Sheet2'!$A$1:$C$18,3,FALSE) But then if I try to do: =VLOOKUP(A1,"'C:\Desktop\"&A2,3,FALSE) A2= [test3.xls]Sheet2'!$A$1:$C$18 It does not work Any idea? Dan |
Dynamic vlookup from a close workbook
The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. Dan wrote: I am trying (very unsuccessfully) to create a vba function that will be like vlookup but will look in other sheets have 3 argument. My problem is how to pass the reference to the other sheet to the function I am aware I can do this without a function: =VLOOKUP(A1,'C:\Desktop\[test3.xls]Sheet2'!$A$1:$C$18,3,FALSE) But then if I try to do: =VLOOKUP(A1,"'C:\Desktop\"&A2,3,FALSE) A2= [test3.xls]Sheet2'!$A$1:$C$18 It does not work Any idea? Dan -- Dave Peterson |
Dynamic vlookup from a close workbook
Thanks. I'd forgotten about that. I've gone away from it becuase all the
workbooks I'm trying to access are on the West coast and I'm on the east coast and it takes FOREVER to update the calculations. I'm guessing that the function opens each workbook and gets the data. I found that if I recalculated, it sometimes took hours to update because I used this function so many times. "Dave Peterson" wrote: The function you'd want to use that's built into excel is =indirect(). But that function returns an error if the sending workbook is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. Dan wrote: I am trying (very unsuccessfully) to create a vba function that will be like vlookup but will look in other sheets have 3 argument. My problem is how to pass the reference to the other sheet to the function I am aware I can do this without a function: =VLOOKUP(A1,'C:\Desktop\[test3.xls]Sheet2'!$A$1:$C$18,3,FALSE) But then if I try to do: =VLOOKUP(A1,"'C:\Desktop\"&A2,3,FALSE) A2= [test3.xls]Sheet2'!$A$1:$C$18 It does not work Any idea? Dan -- Dave Peterson |
Dynamic vlookup from a close workbook
'VBA does not include a method to retrieve a value from a closed file.
'however, you can take advantage of Excel's ability to work with linked files. 'outline code below retrieves values from a defined range in a closed workbook 'and returns them to a worksheet in the active workbook 'Application.WorksheetFunction.VLookup can then be used to lookup required data 'its not elegant but should work! Sub ClosedWBLookUp() Dim rng As Range lookupvalue = ThisWorkbook.Worksheets(1).Range("A1").Value 'location of workbook and worksheet & range 'where the data you want to lookup 'is located mydata = "='C:\Desktop\[test3.xls]Sheet2'!$A$1:$C$18" 'range to return closed workbook data Set rng = ThisWorkbook.Worksheets(2).Range("A1:C18") 'link to worksheet rng.Formula = mydata On Error Resume Next lookupdata = Application.WorksheetFunction.VLookup(lookupvalue, rng, 3, False) If Err = 0 Then MsgBox lookupdata Else MsgBox lookupvalue & " Not Found" End If On Error GoTo 0 'clear links rng.Clear End Sub -- jb "Dan" wrote: I am trying (very unsuccessfully) to create a vba function that will be like vlookup but will look in other sheets have 3 argument. My problem is how to pass the reference to the other sheet to the function I am aware I can do this without a function: =VLOOKUP(A1,'C:\Desktop\[test3.xls]Sheet2'!$A$1:$C$18,3,FALSE) But then if I try to do: =VLOOKUP(A1,"'C:\Desktop\"&A2,3,FALSE) A2= [test3.xls]Sheet2'!$A$1:$C$18 It does not work Any idea? Dan |
All times are GMT +1. The time now is 07:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com