Dynamic Worksheet Lookup
 

Good Day

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

I have a workbook 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

Dynamic Worksheet Lookup
 

See one crack at this in your later posting ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Salman" wrote in message
...
Good Day

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

I have a workbook 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