Good Day

I am trying to define a list of workbooks path and refering lookup
table_array to that list but its not working.

I have a database having 175 workbooks containing data as follows

Workbook name: Results ACBL
Sheet Name: Results

Sheet Details


Results'!

Forecasted and Actual Results

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 workbook I have to call valuation for a specific year from any
specific analysts, like

Workbook name: Result Summary

Co. ABC XYZ .... .... .....
2006 2006
ACBL (F) 12 13
SEPL (F) 14 15
ATIF (F) 16 17

(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 workbook and dynamically, everytime formula need to be edited for the
workbook name saved

in different subfolders.

I have developed a list for the whole 175 companies in a sheet containing
the symbols for the

company (ACBL) in the workbook name and the whole path of that file in the
corresponding cell,

e.g. (S:\Correspondence with Analyst\[Results UPDATES.xls]ACBL'!)

Is there anyway that i can use this list of path as lookup_array to lookup
the value in

different workbook and sheets.

Is there anyway, than it would be really helpful

I have define name for the path list "Path List" and trying the formula with
the help of index

and match.

What i am trying rigth now is only for on row, where

A1 (Sheet Name) B1 (Path)
ACBL 'S:\Correspondence with Analyst\[Results UPDATES.xls]

C1 (combining the 2 cell A1 and B1)
'S:\Correspondence with Analyst\[Results UPDATES.xls]ACBL'!

D1 (lookup Value)
ABC

E1 (Formula)
=vlookup(D1,c1A1:h30,4,false)

Here C1 refers to the workbook path and sheet name and A1:H30 is the cell
range to lookup in

that sheet.

Is there any advise