Help about dynamic value table array in Vlookup

[rong3]

Hi there,
I have stucked on Vlookup like the title.
So example: I have an Excel file contains data to be searched by Vlookup as path: D:\Example\Data.xlsx.
And I have an other Excel with cell B2 that contains value path of Excel data (D:\Example\Data.xlsx.).
What i need is dynamic that B2 cell value into my Vlookup function formula,so I really dont know is there any formula in Vlookup to solved it. I tried as below

=VLOOKUP(A3,INDIRECT(CONCATENATE(B2,"test'!$B:$L")),2,FALSE)

But it does not work. I need all your help. Thank so much.

[rong3]

Bump, any helps?

[mancubus]

value in B2 should be:
D:\Example\[Data.xlsx]
(file name is square bracketed)

=VLOOKUP(A3,INDIRECT("'"&B2&"test'!$B:$L"),2,0) 

Data.xlsx should be open, otherwise this formula will produce #REF! error.



_________________________________________
CONCATENATE(Text1,Text2,Text3,Text4)
is identical to
Text1&Text2&Text3&Text4

[rong3]

Have an other way in VBA to do this without file is open?

[mancubus]

do you only want to get a single cell's value -which meets the search criteria- from a closed workbook into a single cell in the open workbbok?

[rong3]

do you only want to get a single cell's value -which meets the search criteria- from a closed workbook into a single cell in the open workbbok?

Hi I found the solved in VBA getting single like your post, but if i have not permission to access file path, can I get value of that?

[mancubus]

assumptions regarding the sheet "test" in closed excel file:
- has an excel table
- top left cell of the table is A1
- row 1 of the contains the "Column Headers"
- no blank cells in row 1 and col 1 of the table
- no other tables

Sub vbax_61051_cond_pull_a_cell_value_from_closed_workbook()
'Requires a reference to Microsoft ActiveX Data Objects x.x Library (in VBE, Tools / References)

    Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
    Dim ClosedFile As String
    Dim LookUpVal
    
    With Worksheets("Sheet1") 'change Sheet1 to suit
        ClosedFile = .Range("B2").Value
        LookUpVal = .Range("A3").Value
    End With
    
    cn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;" _
        & "Data Source=" & ClosedFile & ";" _
        & "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1""")
    
    rs.Open "SELECT [ColC Header] FROM [test$] where [ColB Header] = " & LookUpVal & ";", cn, adOpenStatic, adLockReadOnly
    
    Worksheets("Sheet1").Range("K15").CopyFromRecordset rs 'change Sheet1 and K15 to suit
    
    rs.Close
    cn.Close

End Sub

[mancubus]

Hi I found the solved in VBA getting single like your post, but if i have not permission to access file path, can I get value of that?

it seems i did not refresh the page at time of posting my message, so i see this post now.

the code in post # 6 will pull a single value, if your table contains a unique item to be found.

if you can upload both of your workbooks (obfuscate the confidential info, if any) i can provide a tailored solution.

[rong3]

it seems i did not refresh the page at time of posting my message, so i see this post now.

the code in post # 6 will pull a single value, if your table contains a unique item to be found.

if you can upload both of your workbooks (obfuscate the confidential info, if any) i can provide a tailored solution.

You have solution for though not permission to access file path? Can you give me a little?
But i cant use OLEDB on VBA. My company not allow me to do this

[mancubus]

you shoud contact the IT department if you can't Access a file.

[rong3]

you shoud contact the IT department if you can't Access a file.

I help for other department that not allow to view file, So I build an Excel to get single value return of it.