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]

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
	B	C	D	E
43	Name of master file		Values
44
45
46	1	a
47	3	c
48	5	e
49
Sheet1

 

[harinsh]

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]

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]

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]

not with a function , I think you may need VBA code to do that - but not certain

there is a function -
Extract data from multiple cells of closed Excel files

but last time I tried it - it did not work on excel 2010

 

[etaf]

i have now added the three addins for direct.ext and got it to work

what version of excel are you running ?

 

[harinsh]

oh...ok, I use 2007 version....

 

[etaf]

you could try indirect.ext - i did get indirect.ext working using the link I posted - which also covers 2007 version

 

[Ashish Mathur]

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.