Good Day

I am trying to minimize the working for every new year, a little help needed

I have a workbookk having 175 worksheet containing data as follows

Workbook name: Results Update
Sheet Name: ACBL

Sheet Details


ACBL
Analyst Name 2005 2006 2007 2008 Last Update on

ABC 10 12 14 16 Nov. 10, 2006
XYZ 11 13 15 17 Nov. 05, 2006

Total 25 analyst names


In other workbooks dedicated for every company like individualy ACBL in i
have to call valuation for the years for that specific company of some
specific analysts, like

Workbook name: Time Series ACBL

ABC XYZ .... .... .....
2006 (A) 12 13
2007 (F) 14 15
2008 (F) 16 17

(A) stands for Actual Result
(F) stands for forecasted Result

Analyst name more often change depends on the situation and need.

I can get the name and corresponding value dynamically by using index and
match but can select the sheet dynamically, everytime formula need to be
edited for the sheet name.

I have developed a list for the whole 175 companies in a sheet containing
the symbols for the company (ACBL) and the whole path in the corresponding
cell, for the workbook Result update with the sheet name e.g.
(S:\Correspondence with Analyst\[Results UPDATES.xls]ACBL'!)

Is there anyway that i can use this path to lookup the value in different
sheets by defining the lookup_array through the list.
Is there anyway than it would be really helpful