Dynamic file selection and vlookup fuction

harinsh

Active Member
Joined
Feb 7, 2012
Messages
259
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.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,777
Office Version
365
Platform
MacOS
you could use an indirect function
so if you put the name of the file into a cell somewhere and then reference that cell as part of the vlookup

so if you put the name of the master data file into a cell - say AA1
then you can use this as the vlookup
=(VLOOKUP(B46,INDIRECT("'["&$AA$1&".xlsx]sheet1'!$A$1:$B$12"),2,FALSE))

each time the master file changes- just copy to the same directory as your main working file
change the name in cell AA1 or whatever cell works for you
and the vlookup should work and pull in the required information

in this example - I have a separate workbook in the same directory called "values" and have in column A 1-6 and column B A-F

Excel Workbook
BCDE
43Name of master fileValues
44
45
461a
473c
485e
49
Sheet1
 

harinsh

Active Member
Joined
Feb 7, 2012
Messages
259
I not sure why I am getting reference error.....below is my example....

=VLOOKUP(J2,INDIRECT("'["&$B$9&"]Workings'!$A:$M"),8,0)

Path = C:\Data format\test1.xlsm and sheet name is "workings"

plz help on this....
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,777
Office Version
365
Platform
MacOS
Your adding Square brackets to all the path

whats in B9 ?


if this is in B9
C:\Data format\test1.xlsm

then you need [] around the file name

format is

'C:\Users\wayne\Documents\0. TSG\Excel\indirect\Indirect from workbook\[settings.xlsx]sheet1'!c30

where you need the ' if there are any spaces in the names

so to reproduce
C:\Data format\test1.xlsm and use the sheet Workings'!$A:$M"

'C:\Data format\[test1.xlsm]Workings'!$A:$M"

so the indirect will depend on what you have in cell
B9

assume its just test1.xlsm

then

"'C:\Data format\[" & B9 & "]Workings'!$A:$M"

indirect (
"'C:\Data format\[" & B9 & "]Workings'!$A:$M" )


=VLOOKUP(J2, indirect ("'C:\Data format\[" & B9 & "]Workings'!$A:$M" ) ,8,0)

you need the ' because data format has a space

the work book
test1.xlsm needs to be open to reference - this will not work for closed workbooks - which may not be what you want


 

harinsh

Active Member
Joined
Feb 7, 2012
Messages
259
Thanks for your help it's working fine....yes I was trying the fuction before opening the file. Is there any other way where I can extract the values apart from this and even if file closed still can able to get the values by using vlooup....
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,777
Office Version
365
Platform
MacOS
i have now added the three addins for direct.ext and got it to work

what version of excel are you running ?
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,777
Office Version
365
Platform
MacOS
you could try indirect.ext - i did get indirect.ext working using the link I posted - which also covers 2007 version
 

Ashish Mathur

New Member
Joined
Mar 10, 2013
Messages
26
Hi,

It seems to work fine for me. May be the Morefunc add-in has not be installed properly. For Excel 2010, the steps for installation are mentioned in step 4 of the same link. Even if after proper installation, it is not working fine (same problem being faced by Simon in the Comments section of the link), then restarting MS Excel may solve the problem.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,544
Messages
5,445,099
Members
405,316
Latest member
joaoamaro

This Week's Hot Topics

Top