dynamic table_array in lookup,match & index

[Guest]

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

 

[Guest]

Here's a crack at this ..

In Results Update.xls, in sheet: ACBL, Analyst names are assumed in A3 down,
years in B2 across. Similar structure is assumed in the other 174 company
sheets.

With Results Update.xls open ..
(this is a must, but should not pose a problem since it's only 1 file)

In: Time Series ACBL.xls, in Sheet1 (say),
Analyst names are assumed listed in C1 across, years in A2 down

Put in A1:
=SUBSTITUTE(SUBSTITUTE(MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1),"Time
Series ",""),".xls","")

The above will extract the company name "ACBL" from the filename into A1.
I've presumed all 175 individual company files are similarly named as: Time
Series XXXX.xls, Time Series YYY.xls, etc, ie with the preceding phrase
inclusive of the space: "Time Series ".

Then place in the top left cell C2:
=OFFSET(INDIRECT("'[Results
Update.xls]"&$A$1&"'!A2"),MATCH(C$1,INDIRECT("'[Results
Update.xls]"&$A$1&"'!A:A"),0)-2,MATCH($A2,INDIRECT("'[Results
Update.xls]"&$A$1&"'!2:2"),0)-1)
Copy C2 across & fill down to populate the table

The above should return the required results
in Sheet1 in the individual company files.