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
"Max" wrote:
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!
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Salman" wrote in message
...
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