Please help me find the proper way to look-up / reference cells in separate XLSX files

I've been searching for a way to reference and/or look up a cell in a separate XLSX file. All of the how-to's I'm finding are related to, or variations on, VLOOKUP and I don't think this is the proper way about doing what I'm trying to achieve here.

In Workbook 1 (CostMatrix-Commodities) that I made to mimic the appearance of my vendors Market Update; it's well laid out as it contains a great deal of information that I couldn't normally print on one page of a spreadsheet table, so I'm reluctant to change the layout. I've defined a name unique to each cell containing a product price.

https://docs.google.com/spreadsheet/...b3c&output=xls

In Workbook 2 (CostLookup-Commodities), I've created a dynamic table that lists all the products in the Cost Matrix, and in one of the columns I've included the defined name of the respective cell. Also, in this dynamic table, I've included a column for Cost. In this column, I want to define a formula that will use the defined name to look up the cell value in the Cost Matrix spreadsheet with the respective defined cell name.

https://docs.google.com/spreadsheet/...VkE&output=xls

Obviously, the links above aren't the real spreadsheets as they contain sensitive information, but the information provided within is a small sampling of the contents.

---

I can't help but wonder if I'm attempting to go about this the wrong/inefficient way. I like using dynamic tables for too many reasons to mention, but maybe I shouldn't make my list in the Cost Lookup workbook into a table and simply do a reference lookup??? I've never been formally schooled in Excel, so I'm learning as I go along.

A huge "THANKS!" in advance to anyone willing to provide a novice with experienced advice or a solution to my quandary.

I think you need INDIRECT. For example:

=INDIRECT("'CostMatrix-Commodities.xlsx'!" & A2)


Regards, TMS

Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.