Vlookup From External Workbook VBA

Hello
This is cross forum post but due to no response I am posting here Link is Here

I want to use vlookup from external file = Data and Worksheet = Support
as I already know how to use vlookup but I do not want to use workbook name and Worksheet name in my formula
this is the existing formula. Please help me to Write formula without using Workbook name and Worksheet Name in Formula

Please Login or Register  to view this content.

Hi

You could use something like this..

Please Login or Register  to view this content.

The only other way would be to use a Quantum Computer with Telepathic AI that would already know what workbook name and Worksheet name you wanted. That would require Office 2070, but it's not out yet for another fifty years.

zeddy

Hi

OK, maybe you could just put the lookup source details into another cell, e.g. cell [K1], and then use the INDIRECT method to refer to it..

Please Login or Register  to view this content.

zeddy

zzzeddy, What if someone changed the file name then how we can handle it , I want that it should be working whatever the filename after the change too

Hi

We would need to know more about the setup.
Probably the best initial way is to prevent any other User from changing the name of the data file.

zeddy

It is impossible to restrict the user to change the filename

Hi

If both workbooks are open in the same Excel session, then any VLOOKUP formula that refers to the other data-workbook would automatically be updated if you changed the sheetname in the other data-workbook, or saved the other data-workbook with a different filename. The VLOOKUP formulas would adjust automatically to show the renamed-filename or renamed-worksheet.

If you subsequently closed that data-workbook, the formulas in the first workbook would now show the full pathname, filename, and sheetname of the data-workbook.

If you closed all workbooks and then subsequently renamed your data-workbook using the file explorer, then VLOOKUP formulas would not 'know' what the new filename is.

zeddy

Agree with your explanation, but consider the scenario that in vb code we have fixed the filename , on the otherday someone changed the filename from folder then

Hi

Yes. Same problem would arise if someone just moved the data-workbook to another folder-location. Or just deleted it.

You can deal with issues like this in vba, for example you could have code that checks for the existence of a specific file in a specified location, and display an appropriate message.

If you explain a little more detail of your setup, we could help more.

zeddy

I do not want to force the user to rename the workbook
Setup is very I am looking from my main workbook(Data) which have vb code and the other workbook is already open and I am inputting vlookup in it