How to search data from old spreadsheet?

[atulrajratna]

I have created one spreadsheet in which i get imp data from other sheets (in the same spreadsheet) automatically by selecting dropdown list, using IF formula. and its working very nicely. Now if i want to pull imp data from worksheet 01.xlsx, 02.xlsx, 03.xlsx from diff folder (drive D) to worksheet A.xlsx (saved on my desktop). by entering only name of the file (01 or 02 or 03)
if i enter 01 in the particular cell in file A.xlsx, i would have to get table from cell A1:C10 of file 01.xlsx
=[01.xlsx]Sheet1!A1:C10
if i enter 02 then the data from particular file should be pulled out.
=[02.xlsx]Sheet1!A1:C10

so the formula remain same, only file name should be change. is it possible?

which formula should i use for it. please help me in this. it will be really very helpful to everyone.
Thank you!!

 

[Aladin Akyurek]

I have created one spreadsheet in which i get imp data from other sheets (in the same spreadsheet) automatically by selecting dropdown list, using IF formula. and its working very nicely. Now if i want to pull imp data from worksheet 01.xlsx, 02.xlsx, 03.xlsx from diff folder (drive D) to worksheet A.xlsx (saved on my desktop). by entering only name of the file (01 or 02 or 03)
if i enter 01 in the particular cell in file A.xlsx, i would have to get table from cell A1:C10 of file 01.xlsx
=[01.xlsx]Sheet1!A1:C10
if i enter 02 then the data from particular file should be pulled out.
=[02.xlsx]Sheet1!A1:C10

so the formula remain same, only file name should be change. is it possible?

which formula should i use for it. please help me in this. it will be really very helpful to everyone.
Thank you!!

=INDIRECT("'"&A1&"'!A1:C10")

where A1 houses something like:

[01.xlsx]Sheet1

INDIRECT requires that the target is open. For closed books, you'd need a function like INDIRECT.EXT, which is available as part of the free morefunc.xll add-in.