Table_array lookup through a define list

[Salman]

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

 

[Max]

Perhaps you could throw a courtesy feedback as a closure to my response in
your earlier posting today. You've dramatically changed your issue
parameters in this new posting. I've got nothing further to offer you here.
Operationally, should you ever decide to revert to the situation as per your
earlier post, think you'd find my suggestion there a workable option.

 

[Salman]

Thanks alot, and pardon for that, infact after posting my query i could not
saw my question on the forum nor i receive any intimation through mail from
the site, although the first one was posted couple of days back, I just
received 3 intimations at the same time, dont know wat happened.
thanks for your effort, now i will go to your reply, i think its neccessary
to thanks you first and now i am going to check your reply for earlier post.
in recent post i try to clear the situation which i thought somehow
confusing, but the situation, i mean the action wat i want to do is the same
and it can be use in any situaion if there is any solution.
Once again thanks alot

Perhaps you could throw a courtesy feedback as a closure to my response in
your earlier posting today. You've dramatically changed your issue
parameters in this new posting. I've got nothing further to offer you here.
Operationally, should you ever decide to revert to the situation as per your
earlier post, think you'd find my suggestion there a workable option.

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

 

[Max]

It's much easier to work with 175 identically structured source sheets in a
single book / file than to work with 175 books, leaving aside 175 books in
175 different folders ..

 

[Salman]

Thanks again for your reply, i think i was not very much clear

what exactly i want to do is retreive data from 175 workbooks in a single
sheet

175 different workbooks in different sub folders -----to------> a single sheet

To confirm, file names are in same format i.e. Time Series xxx.xls, and in
the all workbooks worksheets name are similar as well i.e. Time Series the
only problem for me is to how to deal with different paths of subfolders.

175 companies are distributed to different persons who update informations
and that is also through a system (everything is purely in Excel). Data
structure for a company is as follows:

3 Folders 1. External Research (Market Research)
2. Financial Statements (Soft Copies of statement)
3. Internal Research (Internal Working + Time Series + write ups)

the way we are working different 175 workbooks are neccessary and already we
have don alot more so any change in structure would cost alot.

Main thing i want to do, if i was not clear before,

1) I have a list of all 175 workbook paths in a single sheet
2) I have to retreive value, as stated before, in the same workbook in a
another sheet, where i already have the list of workbooks path


In Sheet2
In Cell A1 the Co. Name (ACBL)
In Cell b1 the path of the time series for that co. (S:\Commercial
Banks\Askari Commercial Bank Limited\Internal Research\Time Series ACBL.xls)

what i was trying is " vlookup(a4,table_array,Col...........)

Instead typing the table array manually i am trying to link the cell B1 as
table_array
Is there any way ?

the same thing i tried to tell before, only the data is change, but if there
was anything different in working then pardon me for that

 

[Max]

In Sheet2

In Cell A1 the Co. Name (ACBL)
In Cell b1 the path of the time series for that co. (S:\Commercial
Banks\Askari Commercial Bank Limited\Internal Research\Time Series
ACBL.xls)

what i was trying is " vlookup(a4,table_array,Col...........)

Instead typing the table array manually i am trying
to link the cell B1 as table_array Is there any way ?

Using INDIRECT, it is possible to link to B1, but it won't work if the
source book is closed.

For eg, this works even when the source book: Results Update.xls is closed:
=VLOOKUP(A4,'D:\Modelling\[Results Update.xls]ACBL'!$E$2:$F$4,2,0)

Assuming B1 contains the path:
D:\Modelling\[Results Update.xls]ACBL'!E2:F4

we could frame it up using INDIRECT to point to B1 as:
=VLOOKUP(A4,INDIRECT("'"&B1),2,0)

but the above only works if the source book: Results Update.xls is
simultaneously open. If the source book is closed, we'd just get #REF!

 

[Salman]

is this easy to open 175 workbooks at time, in this case indirect will not
work, is there any other wayby using any function else than vlookup e.g.
index , match e.t.c in which a cell content can be treated as table_array or
workbook and sheet location.
if indirect is the only way then it seems better to do one time dog work and
past the location in every formula manually to avoid opening of 175 workbooks
to update the values.
If anything could work than please it would be really helpfull

In Sheet2
In Cell A1 the Co. Name (ACBL)
In Cell b1 the path of the time series for that co. (S:\Commercial
Banks\Askari Commercial Bank Limited\Internal Research\Time Series
ACBL.xls)

what i was trying is " vlookup(a4,table_array,Col...........)

Instead typing the table array manually i am trying
to link the cell B1 as table_array Is there any way ?

Using INDIRECT, it is possible to link to B1, but it won't work if the
source book is closed.

For eg, this works even when the source book: Results Update.xls is closed:
=VLOOKUP(A4,'D:\Modelling\[Results Update.xls]ACBL'!$E$2:$F$4,2,0)

Assuming B1 contains the path:
D:\Modelling\[Results Update.xls]ACBL'!E2:F4

we could frame it up using INDIRECT to point to B1 as:
=VLOOKUP(A4,INDIRECT("'"&B1),2,0)

but the above only works if the source book: Results Update.xls is
simultaneously open. If the source book is closed, we'd just get #REF!

Thanks again for your reply, i think i was not very much clear

what exactly i want to do is retreive data from 175 workbooks in a single
sheet

175 different workbooks in different sub folders -----to------> a single
sheet

To confirm, file names are in same format i.e. Time Series xxx.xls, and in
the all workbooks worksheets name are similar as well i.e. Time Series the
only problem for me is to how to deal with different paths of subfolders.

175 companies are distributed to different persons who update informations
and that is also through a system (everything is purely in Excel). Data
structure for a company is as follows:

3 Folders 1. External Research (Market Research)
2. Financial Statements (Soft Copies of statement)
3. Internal Research (Internal Working + Time Series + write ups)

the way we are working different 175 workbooks are neccessary and already
we
have don alot more so any change in structure would cost alot.

Main thing i want to do, if i was not clear before,

1) I have a list of all 175 workbook paths in a single sheet
2) I have to retreive value, as stated before, in the same workbook in a
another sheet, where i already have the list of workbooks path


In Sheet2
In Cell A1 the Co. Name (ACBL)
In Cell b1 the path of the time series for that co. (S:\Commercial
Banks\Askari Commercial Bank Limited\Internal Research\Time Series
ACBL.xls)

what i was trying is " vlookup(a4,table_array,Col...........)

Instead typing the table array manually i am trying to link the cell B1 as
table_array
Is there any way ?

the same thing i tried to tell before, only the data is change, but if
there
was anything different in working then pardon me for that

 

[Max]

I'm out here, sorry. Perhaps other responders would have other views to
offer you. Good luck !

 

[Salman]

Thanks for your precious time & efforts

I'm out here, sorry. Perhaps other responders would have other views to
offer you. Good luck !

 

[Max]

Thanks for your precious time & efforts

No prob. Sorry that I couldn't be of further direct help. In case nobody
else jumps in here, I found this recent posting by Dave Peterson which might
be of interest / relevance to you:
--- ---

" Harlan Grove wrote a UDF called PULL
that will retrieve the value from a closed workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

I assume then that there is no way to link dynamically to a closed
workbook without using some kind of macro.

---- ---

I'm afraid I don't have experience using either of the above, so I'm not
sure whether it'll work for your intents here ..

 

[Salman]

thanks alot, i will see if these would solve the issue.