Thank you very much for VBA code. It is working for only cell A1 , I will be
greatful to you if you help me for range of cells and more than one file
because I have files book1,book2 etc
once again thank you very much

"carlo" wrote:

Hi Narasimha, i didn't find your other post.
What you are looking for is probably the indirect function
=INDIRECT(A1 & ".xls!B1")

In A1 you put your bookname for example "test"
then excel goes and looks in test.xls!B1 and returns you
the value of that cell.

That only works, if the other book is open, otherwise you
have to do it by VBA.
You may want to do this everytime the cell A1 changes:
right click on the worksheet-tab and "view code"
there you enter following code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_WB

If Target.Address = "$A$1" Then
Dim main_wb As Workbook
Dim xl_wb As Workbook

Set main_wb = ActiveWorkbook

Set xl_wb = Workbooks.Open("C:\temp\" &
main_wb.Sheets(1).Range("A1"))
main_wb.Sheets(1).Range("B1").Value =
xl_wb.Sheets(1).Range("A1").Value
xl_wb.Close (False)
End If

Exit Sub

Err_WB:
MsgBox "Couldn't find the Workbook, check your spelling"
End Sub

hth carlo