how i can refer to cell in long formula go to another file

[need a help]

='C:\Documents and Settings\VENUS\Desktop\[1.xlsx]1'!$A$1
this is a formula in the specific cell but i have more than 150
files.xlsx
and i have to merge it in one sheet can any one give me a way to make
this idea possiable
a b c d e
1 1xlsx 2.xlsx 3.xlsx 4.xlsx 5.xlsx
2 ='C:\Documents and Settings\VENUS\Desktop\[A1]1'!$A$1
3 ='C:\Documents and Settings\VENUS\Desktop\[B21]1'!$A$1
4 ='C:\Documents and
Settings\VENUS\Desktop\[B3]1'!$A$1
that mean i will refer the name of the file in the furmula to the cell A1
B1 C1 etc.
i have tried many ways but it doesn't run
so kindly and expert tell me the way to do that with the file name and
the
sheet that i give (1) for it names also in the same way

 

[Dave Peterson]

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

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

=============
You may want to consider building formulas that create strings that look like
those formulas.

Like:
="$$$$$='C:\Documents and Settings\VENUS\Desktop\["&A1&"]1'!$A$1"

With 1.xlsx in A1, the formula will evaluate to:
$$$$$='C:\Documents and Settings\VENUS\Desktop\[1.xlsx]1'!$A$1

Then you convert these formulas to values.

And finally, you can select the range
edit|replace
what: $$$$$=
with: =
replace all

Excel will now look at the cells and see that they contain formulas--and excel
will reevaluate them.

I'd do it on a small range first. If you make a mistake and the file doesn't
exist, you'll be dismissing lots and lots of dialogs asking what file you really
meant.

='C:\Documents and Settings\VENUS\Desktop\[1.xlsx]1'!$A$1
this is a formula in the specific cell but i have more than 150
files.xlsx
and i have to merge it in one sheet can any one give me a way to make
this idea possiable
a b c d e
1 1xlsx 2.xlsx 3.xlsx 4.xlsx 5.xlsx
2 ='C:\Documents and Settings\VENUS\Desktop\[A1]1'!$A$1
3 ='C:\Documents and Settings\VENUS\Desktop\[B21]1'!$A$1
4 ='C:\Documents and
Settings\VENUS\Desktop\[B3]1'!$A$1
that mean i will refer the name of the file in the furmula to the cell A1
B1 C1 etc.
i have tried many ways but it doesn't run
so kindly and expert tell me the way to do that with the file name and
the
sheet that i give (1) for it names also in the same way