PDA

View Full Version : Help about dynamic value table array in Vlookup



rong3
10-18-2017, 12:31 AM
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
10-18-2017, 05:55 PM
Bump, any helps?

mancubus
10-18-2017, 11:55 PM
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
10-19-2017, 12:50 AM
Have an other way in VBA to do this without file is open?

mancubus
10-19-2017, 01:17 AM
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
10-19-2017, 01:20 AM
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
10-19-2017, 02:33 AM
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
10-19-2017, 02:55 PM
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
10-19-2017, 06:01 PM
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
10-20-2017, 02:19 AM
you shoud contact the IT department if you can't Access a file.

rong3
10-20-2017, 09:26 PM
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.