I will explain the task I am doing first.
I download a workbook of all closing stock prices each day from an external web site. Each day’s workbook contains one worksheet with the same three columns of data – Company Name, Company EPIC Code & Closing Share Price.
My aim is to create a Workbook, which collates the Closing Share Price data on consecutive columns on one worksheet.
As new companies are added on a daily basis, the daily workbook of closing share prices will not have the same company’s data on the same row within each worksheet/book.
What formula can I create in my own collation workbook to retrieve the data from the daily closing price workbooks based on the unique Company EPIC Code column?
I know about linking workbooks, so linking is not a problem.
As an example of the task I am trying to achieve:
If a company has the Company EPIC Code of AAS, my worksheet will have a row in the first column as AAS. The second column will check the Company EPIC Code column of the Daily Closing Share Price worksheet/book for the row with the entry AAS and retrieve the closing share price value from the corresponding row in the Closing Share Price column.
It would be preferable for the formula to get the Company EPIC Code from the first column of my own worksheet, rather than me just typing the three letter code within the formula.
Your help would be much appreciated.
Thank you
