In some cases opening your Excel workbooks in Access is a better solution than trying to link them through VLookUp formulas. If you have access to Access, try this.
What I have:
- An Orders workbook that shows company code, product code and quantity purchased
- A Company workbook that has the company name
- Another with the product code, product description and the price
What I want: An extended price sheet for each transaction showing the order total. Include the Zip code of the company.
In Access, create a new blank database and delete the default blank table. Then on the External Data tab, click the Excel button in the Import & Link group. Be sure to click the Link to the data source… option! Do this for each workbook.
You will need to tell Access what fields relate to what other fields in the separate workbooks. In this case, Prod and Product mean the same thing; Bill to Co and Cust Code mean the same thing. This is done in the Database Tools tab with the Relationships button. Add all three linked tables to the screen and click and drag each field to its matching field in another table.
Now, we create the Query. Click on the Create tab and the Query Wizard button. Select Simple Query and add the fields you want in the report.
Now, we create the formula. On the Home tab in the Views group, change to the Design view. Right click in the next empty column in the query and choose Build to build an expression. Double click the qty field; type an * for multiply; double click the Price field.
Click OK and then the View button in the Ribbon to see the results. That’s it!