Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 10401
  • Last Modified:

How to make an Excel VBA vlookup when the tables are on another workbook?

How would I go about making a macro that uses vlookup formulas when the tables are on another workbook?
If you look at the BHNRES-Unrecovered-Equipment-TEST VBA file on the 2nd tab it shows the results that I am looking for.
The macro I made is asking to open up the workbook where the tables are on...I do not want it to do this. Is there another to do this?

Jimi Sherman
Jimi Sherman
1 Solution
Glenn RayExcel VBA DeveloperCommented:
It's easier to set up cross-workbook references when both files are open.  Once you create the VLOOKUP formulas correctly, Excel will retain the filepath of the referenced (and possibly closed) workbook in the formula.  Of course, if the source file is moved or deleted, this won't work.

So, the raw formulas in the "Example VBA Results" sheets should be (copied down):
C2: =VLOOKUP(A2,[BrightHouse_Item_Code_Reference.xlsx]Codes'!$A:$B,2,FALSE)
E2: =VLOOKUP(G2,[BrightHouse_Item_Code_Reference.xlsx]Codes'!$D:$E,2,FALSE)

If you wanted to create the formula without the source file ("BrightHouse_Item....") open, you would need to include the full drive:\filepath in front of the first square bracket, like so:
Also note the single apostrophe around the path, filename, and sheet name (needed because of spaces, but good practice).

If you are trying to insert this in VBA, it's no different than inserting a regular formula, and since you have no quotation marks, that won't be an issue.  For example:
Range("C2").Formula = "=VLOOKUP(A2,[BrightHouse_Item_Code_Reference.xlsx]Codes'!$A:$B,2,FALSE)"

Open in new window

or with the full path,
Range("C2").Formula = "=VLOOKUP(A2,'C:\C:\BrightHouseDirectory\Subdirectory\Subdir2\[BrightHouse_Item_Code_Reference.xlsx]Codes'!$D:$E,2,FALSE)

Open in new window

Hope that helps,
Jimi ShermanAuthor Commented:

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now