Vlookup Macro (based on cell values)

[James bolton]

Hi,

Does anyone know a macro that will create a Vlookup on Cell D1 where the filename is cell B2 "/" B3 ".xls" ?

Many Thanks
JOBO

 

[vletm]

@James bolton
You would get much better answer with sample file.

 

[Marc L]

Hi !

And as you have already been warned, better is to post in proper forum !
Here is the Excel formula forum, not the VBA Macros …
(Done : thread moved to appropriate forum …)

You can solve your need just by activating Macro recorder and
enter your formula within a cell !
​

 

[James bolton]

Yes but I guess what I'm trying to say is how do I make the formula work ?

Where cell B2 is C:\Users\Desktop\ & cell B3 is File1 the formula =VLOOKUP(B5,B3&B4&".xls"!A1:B2,2,false) doesn't work ?

I assume this would only be workable via macro ?

 

[Khalid NGO]

Yes but I guess what I'm trying to say is how do I make the formula work ?

Where cell B2 is C:\Users\Desktop\ & cell B3 is File1 the formula =VLOOKUP(B5,B3&B4&".xls"!A1:B2,2,false) doesn't work ?

I assume this would only be workable via macro ?

Hi James,

Not sure, but I think you will need Indirect here:

=VLOOKUP(B5,INDIRECT(B2&B3&".xls!A1:B2"),2,FALSE)

Regards,

 

[vletm]

@James bolton - tested my self - for Your testing ...
B2 needs full path.
Your source (B3) have to be open or You'll get #REF
It needs Sheet-name like Sheet1.
=VLOOKUP(B5,INDIRECT("'"&B2&"["&B3&".xls]Sheet1'!a1:b2"),2,FALSE)

 

[James bolton]

@James bolton - tested my self - for Your testing ...
B2 needs full path.
Your source (B3) have to be open or You'll get #REF
It needs Sheet-name like Sheet1.
=VLOOKUP(B5,INDIRECT("'"&B2&"["&B3&".xls]Sheet1'!a1:b2"),2,FALSE)

Thanks,

I have added a macro that opens the file and then closes it after applying the formula.

However to go one up from this - the formula has been modified via a macro so that it works for every cell within my range;

 ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-4],INDIRECT(""'""&RC[-4]&RC[-3]&""\""&RC[-3]&"".xls'!A1:T10000""),2,FALSE)"

However, I am struggling to copy the methodology when looking for the file to open and close.

Currently my macro is;

  Workbooks.Open Filename:= _
        "C:\User\Test\Test\Test.xls"

However I'd like it to open as

"C:\User\Test"&(RC[-4])&(RC[-3]&.xls

I need the filename to always be 4 cells before then 3 cells before the current cell in my range ?

Any tips ?

If my explaination is too complicated I can upload an example

Many Thanks

JOBO

 

[vletm]

@James bolton
Did You even tried 'my formula'?
Your formula isn't same!
The 2nd part (table_array) of VLOOKUP have to be correct!
Steps:
1) Open Target-file
2) Use formula
3) Close Target-file if no need.
Maybe,
it's better that You'll upload Your sample file ...

 

[James bolton]

Hi

It's modified so that it can continue looping within a range

See attached test example.

Thanks

 

[vletm]

@James bolton
Modifications sometimes works ... sometimes not!
I tried to do one sample for this case.
... Information are missing, so fill those before press the button.