Dynamic file selection and vlookup fuction

[harinsh]

Hi Team,

I have one requirement hope can anyone help with the same.

I will receive one master file which contains the table and it's values and I have another working sheet where I will update the require details. Every time I will copy the entire master data into working file in order to extract the values by using vlookup function. Plz note that the master file has different names each time. So, that if I use vlookup by taking master data path but not getting the results.

I am looking one macro code to select file (master data) and paste the values based on vlooup references in workings sheets. Note this master data file name may change but reference range always same.

Thanks you.

 

[etaf]

Ashish Mathur

Hi, are you the author of the site , I had been trying to use on Excel 2010 a few times on / off and this is the first site that actually explained what needed to be done

 

[Ashish Mathur]

Hi,

Yes, I am. Is the function now working fine?

 

[etaf]

It is for me,

But need a reply from - harinsh

 

[timkay24]

The following is a simplified macro I created (described in laymans terms) which, when the file path is entered in column A and the file name in column B and the macro is run, it takes the values from A & B of the current row and writes the vlookups into that row for you. All that's needed to create a new row for a new file is to enter the path and file name into A & B, run the macro and this will do the rest for you. Much quicker than using the pull function and an option for those who can't have 100 spreadsheets open at the same time or download add-ons to their work computers.

Option Explicit
Sub create_dynamic_vlookup()


'Created by Tim Kay
'This macro searches columns A & B within the selected row for the file destination and file name and creates
'new vlookups based on the information within these cells. To use, simply enter a new file path into columns A and B
'of the selected row(no square brackets are needed)
'and run this macro. Edit the formulas, arrays and cell locations as required.


Dim Destination As String
Dim Filename As String


'set value of cell in column A of current row as "Destination"
Range("A" & (ActiveCell.Row)).Select
Destination = ActiveCell.Value

'set value of cell in column B of current row as "Filename"
Range("B" & (ActiveCell.Row)).Select
Filename = ActiveCell.Value


'Specify which column within the current row you wish to paste the formula into (Edit the letter i.e. "C")
Range("C" & (ActiveCell.Row)).Select


'Formula to be pasted (Ensure that the file format is correct i.e. xlsx
ActiveCell.Value = "=VLOOKUP($A3,'" & Destination & "[" & Filename & ".xlsx" & "]Sheet1'!$A$1:$D$1,1,False)"


'Edit and repeat as required
Range("D" & (ActiveCell.Row)).Select
ActiveCell.Value = "=VLOOKUP($A3,'" & Destination & "[" & Filename & ".xlsx" & "]Sheet1'!$A$1:$D$1,2,False)"


Range("E" & (ActiveCell.Row)).Select
ActiveCell.Value = "=VLOOKUP($A3,'" & Destination & "[" & Filename & ".xlsm" & "]Sheet1'!$A$1:$D$1,3,False)"


Range("F" & (ActiveCell.Row)).Select
ActiveCell.Value = "=VLOOKUP($A3,'" & Destination & "[" & Filename & ".xlsx" & "]Sheet1'!$A$1:$D$1,4,False)"


Range("G" & (ActiveCell.Row)).Select
ActiveCell.Value = "=VLOOKUP($A3,'" & Destination & "[" & Filename & ".xlsx" & "]Sheet1'!$A$1:$D$1,5,False)"


End Sub

 

[timkay24]

.