Vlookup from another workbook which is closed

Question

Hi,

I am using vlookup in excel and fetching the data from another workbook, I want to make a cell reference for second parameter so that it could be dynamic like I can change the workbook name or the Columns. Please suggest

=VLOOKUP(A1,'C:\Users\ajay\desktop[Workbook1.xlsx]Sheet1'!$A:$B,2,0)

Answer 1

Out of below what you want to keep dynamic -

'C:\Users\ajay\desktop[Workbook1.xlsx]Sheet1'!$A:$B

Also let me know which cells do you want to store your dynamic values...

Answer 2

Ciao,

for column you can use a named range and refer it into the formula:

https://support.office.com/en-us/article/Define...

So you can simply change the referred cells in the name range (Formulas->Name Manager) and your formula will continue working without any change.

For workbook name is a bit more complex.

A workaround could be just changing the name of the new workbook that you want to use to match the old one in the formula.

A full-working solution will involve using VBA: you can assign a macro to a button that will write a function in a cell based on user-provided information.

Please let me know if I helped you somehow or if you need further help with VBA.

Thanks

Ugo

Answer 3

Out of below what you want to keep dynamic -

'C:\Users\ajay\desktop[Workbook1.xlsx]Sheet1'!$A:$B

Also let me know which cells do you want to store your dynamic values...

The Workbook path and the Range of the worksheet