Well, it does work for me. This is something not easily demonstrated in a
sample file since your referencing several other files.

???

--
Biff
Microsoft Excel MVP


"need a help" wrote in message
...
i done with the sheet name before but it dosn't work

can you send me an excel sheet with same you done to see how it will work
great thanks for you

"T. Valko" wrote:

Ooops!

I left out the sheet name.

Enter this formula in A2:
="='C:\Documents and Settings\VENUS\Desktop\["&A1&"]'!$A$1"

Formula should be:

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


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this:

Enter this formula in A2:

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

Copy across as needed.

This will result is a TEXT string that looks like a formula.

='C:\Documents and Settings\VENUS\Desktop\[1.xlsx]'!$A$1

Select all the cells that hold these formulas
Goto EditCopy
Then, EditPaste SpecialValues
Then, EditReplace
Find what: =
Replace with: =
Replace All

That's correct. You want to replace the = with an =.


--
Biff
Microsoft Excel MVP


"need a help" wrote in message
...
='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